(─━┘_└━─)/ 머리만1톤(Head1Ton)

큰 값 데이터 형식에 대한 기본적인 내용은 이미 앞에서 한번 소개드렸습니다. 오늘은 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

n       ‘text in row’ 옵션

‘text in row’ 옵션을 설정하면, Text, ntext, image 열의 행에 저장할 최대 크기를 지정할 수 있습니다. 기본값은 256바이트이고, 값의 범위는 24에서 7000바이트입니다. 다음은 Orders 테이블의 text 열에 저장할 데이터를 1000바이트로 지정합니다.

[중요] 이 기능의 ‘text in row’ 옵션은 이후 버전의 SQL Server 에서 제거될 예정입니다. 큰 값의 데이터를 저장하기 위해서는 varchar(max), nvarchar(max), varbinary(max) 데이터 형식을 사용할 것을 권고합니다.

n       ‘large value types out of row’’ 옵션

sp_tableoption을 사용하면 행 외부 옵션을 설정 및 해제할 수 있습니다.

varchar(max), nvarchar(max) 및 varbinary(max)를 큰 값 데이터 형식이라고 하며, 큰 값 데이터 형식을 사용하면 최대 2³¹-1바이트의 데이터를 저장할 수 있습니다. sp_tableoption 저장 프로시저의 'large value types out of row' 옵션을 OFF로 설정하면 큰 값 형식의 행 내부 저장 용량 제한은 8,000바이트입니다. 이 옵션을 ON으로 설정하면 행 내부에 16바이트 루트가 저장됩니다.

대부분의 쿼리문이 큰 값 데이터 형식 열을 참조하지 않는 테이블에 대해서는 이 옵션을 ON으로 설정하는 것이 좋습니다. 이러한 열을 행 외부에 저장하면 각 페이지에 더 많은 행이 들어갈 수 있으므로 테이블을 검색하는 데 필요한 I/O 작업 수가 줄어듭니다. 이 옵션의 값을 OFF로 설정하면 각 페이지에 저장되는 데이터 행의 수가 줄어들어 쿼리를 처리하기 위해 읽어야 하는 페이지의 수가 늘어날 수 있습니다.

다음 문장 중에 하나를 사용하여 MyTable 테이블에 행 외부 옵션을 설정합니다. sp_tableoption의 설정값은 true, on, 1 이고 해제값은 false, off, 0 입니다.

large value types out of row 옵션 값이 변경되는 경우, 기존 varchar(max), nvarchar(max), varbinary(max) 및 xml 값은 즉시 변환되지 않고, 문자열의 저장소는 다음에 업데이트될 때 변경됩니다.