[SQL 2005 GUIDE] varchar(max) 열이 행 내에 저장되는지 확인하는 스크립트

큰 값 데이터 형식에 대한 기본적인 내용은 이미 앞에서 한번 소개드렸습니다. 오늘은 varchar(max)를 대상으로 테스트해 본 스크립트를 올려 봅니다.

 

다음은 varchar(max)가 실제로 데이터 행과 함께 저장되는지를 제 눈으로 확인하기 위해 DBCC PAGE와 DBCC EXTENTINFO 명령어를 사용하여 작성해 본 스크립트입니다. 다음 스크립트를 테스트해 보면 varchar(max) 데이터 형식의 내부 표현을 확인할 수 있습니다.

 

 

 

CREATE DATABASE NewDatatype

GO

USE NewDatatype

GO

CREATE TABLE BigTable (id int, col1 varchar(25),

     col2 varchar(max), col3 varchar(max))

GO

INSERT INTO BigTable VALUES (1, 'Row1', 'AAAAAAAAAA', 'aaaaaaaaaa')

INSERT INTO BigTable VALUES (2, 'Row2', 'BBBBBBBBBB', 'bbbbbbbbbb')

GO

SELECT * FROM sysindexes

WHERE id=object_id('BigTable')

GO

DBCC EXTENTINFO ('NewDatatype', 'BigTable')

GO

--> file_id = 1

--> PAGE_id = 40

DBCC TRACEON (3604)

GO

DBCC PAGE ('NewDatatype', 1, 40, 3)

GO

 

UPDATE BigTable SET col2 = replicate('A', 8000) WHERE id = 1;

GO

DBCC PAGE ('NewDatatype', 1, 40, 3)

GO

 

UPDATE BigTable SET col2 = replicate('B', 4000) WHERE id = 2;

GO

DBCC EXTENTINFO ('NewDatatype', 'BigTable')

GO

DBCC PAGE ('NewDatatype', 1, 40, 3)

GO

DBCC PAGE ('NewDatatype', 1, 80, 3)

GO

 

 

 

위의 스크립트를 활용하여 'large value types out of row' 옵션을 적용하는 경우에는 내부적으로 어떻게 저장되는지 확인해 보겠습니다.

 

'large value types out of row' 의 값이 1이면, varchar(max), nvarchar(max), varbinary(max), xml 컬럼들이 행의 외부에 저장되며 행의 내부에는 root에 대한 16바이트 포인터를 가집니다. 0이면 varchar(max), nvarchar(max), varbinary(max), xml 값이 데이터 행에 직접 저장됩니다.

 

 

 

-- 1) 'large value types out of row' 옵션 활성화

EXEC sp_tableoption N'BigTable', 'large value types out of row', 'ON';

GO

 

 

DBCC PAGE ('NewDatatype', 1, 40, 3)

GO

 

-- 2) UPDATE를 실행해야 실제로 옵션 활성화한 효과가 적용됩니다.

UPDATE BigTable SET col2 = replicate('A', 20)

WHERE id = 1;

GO

DBCC PAGE ('NewDatatype', 1, 40, 3)

GO

DBCC EXTENTINFO ('NewDatatype', 'BigTable')

GO

DBCC PAGE ('NewDatatype', 1, 89, 3)

GO

 

 

-- 3) 'large value types out of row' 옵션 비활성화

EXEC sp_tableoption N'BigTable', 'large value types out of row', 'OFF';

GO