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

/****************************************************************

             varchar(max) +  청크업데이트(Chunk Update) 사용

****************************************************************/

--테스트용테이블변수정의

DECLARE @T TABLE (V VARCHAR(MAX))

INSERT INTO @T VALUES ('')

DECLARE @I INT, @S DATETIME

SET @S = GETDATE()

SET @I = 0

WHILE @I < 10000

BEGIN

             UPDATE @T

             SET V.WRITE (REPLICATE('A',10),NULL,NULL)

             SET @I = @I + 1

END

SELECT DATEDIFF(MS,@S,GETDATE()), LEN(V) FROM @T

GO

--826     100000 : 수행시간0.826초, V 컬럼의길이100,000

/****************************************************************

             varchar(max) +  '+' 연산자사용

****************************************************************/

DECLARE @T TABLE (V VARCHAR(MAX))

INSERT INTO @T VALUES ('')

DECLARE @I INT, @S DATETIME

SET @S = GETDATE()

SET @I = 0

WHILE @I < 10000

BEGIN

             UPDATE @T

             SET V = V + REPLICATE('A',10)

             SET @I = @I + 1

END

SELECT DATEDIFF(MS,@S,GETDATE()), LEN(V) FROM @T

GO

--7343   100000 : 수행시간7.3초, V 컬럼의길이100,000

    테스트 환경 : 2794 Mhz * 8 CPU,   8 GB Memory,  Win 2003 SP1        
****************************************************************/

text, image 데이터 형식에 비해 큰 값 데이터 형식이 가지는 장점 중 한가지가 큰 값 데이터 형식에는 문자열에 사용 가능한 문자열 함수들을 사용할 수 있다는 것입니다. 그렇지만 문자열 함수를 사용할 때 유의해야 할 점이 있습니다.

명시적으로 그 문자열 함수를 적용하고자 하는 식이 varchar(max)라는 것을 알려 주지 않으면, 문자열 함수는 기본적으로 식을 문자열 데이터 형식으로 간주한다는 점입니다.

예를 들어 다음 스크립트를 보면 @max 라는 로컬 변수는 varchar(max)로 정의되어 있습니다. 그런데 replicate 함수를 사용하여 10000 바이트 크기의 문자열을 만들어서 @max 변수에 저장하려고 하면 실제로는 @max 변수에 8천 바이트만 저장됩니다. 문자열 함수의 결과를 저장할 그릇을, 문자열 데이터 형식을 기준으로 준비하는 것으로 판단됩니다. 그래서 함수 결과가 문자열 데이터 형식의 최대 크기인 8000 바이트까지만 인식하는 거죠.

DECLARE @max varchar(max)

SELECT @max = replicate('1234567890', 1000)

SELECT @max, datalength(@max)

GO

--> 8000 바이트

만일 다음과 같이 CAST('1234567890' AS varchar(max) 라고, 식이 varchar(max)라는 것을 SQL Server에게 미리 알려 주면 이 replicate 함수는 다음과 같이 10000 바이트를 정상적으로 처리합니다.

DECLARE @max varchar(max)

SELECT @max = replicate(CAST('1234567890' AS varchar(max)), 1000)

SELECT @max, datalength(@max)

GO

--> 10000 바이트

문자열 결합 연산자인 + 도 마찬가지입니다.

다음 예에서와 같이 replicate 함수를 적용한 다음에 varchar(max)로 변환해 보았자 이미 8000 바이트까지만 인식되었기 때문에 데이터가 8천 바이트까지만 인식됩니다.

DECLARE @max varchar(max)

SELECT @max = CAST(replicate('max test 1', 800) +

                   replicate('max test 2', 800) AS varchar(max))

SELECT datalength(@max)

GO

--> 8000 바이트

다음과 같이 + 연산자를 적용할 식 중 하나라도 varchar(max)로 정의하거나, + 연산자를 적용할 식 중 하나에 CAST 함수를 적용하면 원하는 결과를 얻을 수 있습니다.

DECLARE @max1 varchar(max), @max2 varchar(max), @max3 varchar(max)

SELECT @max1 = replicate('max test 1', 800)

SELECT @max2 = replicate('max test 2', 800)

SELECT @max3 = @max1 + @max2

SELECT datalength(@max3)

GO

-- 16000

DECLARE @max varchar(max)

SELECT @max = CAST(replicate('max test 1', 800) AS varchar(max))

  + replicate('max test 2', 800)

SELECT datalength(@max)

GO

-- 16000

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

큰 값 데이터 형식

text, ntext, image 데이터 형식 대신 varchar(max), nvarchar(max), varbinary(max) 데이터 형식을 사용하는 것을 권고합니다.

SQL Server 2005에서 max 지정자가 새롭게 도입되어 varchar, nvarchar, varbinary 데이터 형식의 저장 기능이 확장되었습니다. varchar(max), nvarchar(max), varbinary(max)를 큰 값 데이터 형식이라고 합니다. 큰 값 데이터 형식을 사용하면 최대 2^31-1 바이트의 데이터를 저장할 수 있으면서, 동작은 기존의 varchar, nvarchar, varbinary의 동작과 유사하기 때문에 SQL Server에서 크기가 큰 문자열, 유니코드, 이진 데이터를 보다 효율적으로 저장하고 검색할 수 있게 되었으며 큰 값 데이터 형식을 사용하면 text, ntext, image 데이터 형식으로는 불가능한 일들이 가능하게 됩니다.

다음 표는 큰 값 데이터 형식과 이전 버전의 SQL Server에서 제공하는 데이터 형식의 관계를 보여 줍니다.