VARCHAR(MAX)

SQL Servier 2005부터 'MAX' 지시어가 소개되어, 컬럼에도 큰사이즈의 데이터를 넣을수 있게 되었습니다. 이전 버전에서는 한 레코드의 크기가

한 페이지 (8Kbyte)를 못 넘게 되어 있으므로, varchar(8000) 또는 nvarchar(4000)이 한계였기 때문에 TEXT,NTEXT,IMAGE 를 사용해야 했습니다.

하이만 이런 데이터 타입의 컬럼들은 Update하거나 검색할 때, 일반적인 데이터 타입의 컬럼과 다른 방법으로 작업을 해야 하였습니다. 하지만

MAX 지시어의 등장으로 아주 큰 객체도 다른 것과 똑 같이 사용할 수 있게 되었습니다.

기존 TEXT를 조작하는 SQL 문을 보도록 하겠습니다.
TEXT형의 컬럼인 pr_info 에서 어떤 정보를 10자만 추출할 때, 일반 컬럼 같으면 substring을 사용하지만, TEXT 컬럼에서는 사용 할수 없기 때문

에 아래와 같이 사용 해야 하였습니다.

DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(pr_info) 
FROM  pubs.dbo.pub_info
WHERE pub_id = ' 0877'
READTEXT pub_info.pr_info @ptrval 0 10;

TEXT 형 컬럼의 앞 4자리의 값을 'This'로 Update할려고 아래와 같이 사용
해야 했습니다.

DECLARE @ptrval varbinary (16)
SELECT @ptrval = TEXTPTR(pr_info) 
FROM pubs.dbo.pub_info
WHERE pub_id = '0877'
UPDATETEXT pub_info.pr_info @ptrval 0 4 'This'

SQL Server 2005부터는 TEXT, NTEXT, IMAGE 를 사용하지 않고 MAX 특정자를 사용 할수 있게 되었습니다. 그럼 MAX 특정자(Specifier) 는 VARCHAR

와 VARBINARY는 2^31-1 byte 까지 지원하고, NVARCHAR는 2^30-1 byte까지 늘릴 수 있다. (대략 2GB) VARCHAR(MAX), NVARCHAR(MAX), VARBINARY

(MAX) 를 통칭해서 Large-Value 데이터 형이라 말합니다.

Test 를 위해 테이블을 만들어 보겠습니다.

CREATE TABLE dbo.LargeDataType
(
 LargeColumn VARCHAR(MAX)
)

여기서 LageColumn 컬럼에 데이타를 넣는다고 가정할 때, 기존 VARCHAR와 마찮가지로, 값의 크기가 8000자 이하라면, 기존 VARCHAR와 같이 테이

블 내에 저장하고, 그보다 큰 크기를 가지는 값이라면 TEXT처럼 테이블 외에 저장하고, 메모리 주소만 varbinary(16)형태로 테이블에 저장할 것

입니다. 또 하나는 row size가 한페이지 크기 (8060 byte)보다 작으면 VARCHAR(MAX) 의 크기가 8000 보다 크더라도, 테이블의 페이지에 저장되고

, row size가 한 페이지 크기보다 크다면 VARCHAR(MAX)는 TEXT처럼 취급하여 외부에 저장합니다.

이렇게 VARCHAR(MAX)의 저장 장소를 자동으로 결정하는 게 아니라 무조건 외부에 저장하게 끔 하려면 다음과 같이 sp_tableoption을 사용하면 됩

니다.

EXEC sp_tableoption 
'dbo.LargeDataType', 'large value types out of row', 1

LargeDataType 테이블은 앞으로 모든 Large-Object(LOB)는 TEXT처럼 외부에 저장되고, 저장소위치만 테이블에 기록됩니다.

Large-Object의 값이 테이블에 다른 컬럼들과 같이 저장할 때의 장점은, 한 행의 반환 시 한번의 I/O 동작만 수행하면 된다는 것입니다. TEXT 처

럼 외부에 저장된 데이터는 따로 I/O가 동작하므로, 많은 양의 ResultSet을 가져올 때는 많은 양의 I/O 동작이 발생하여 성능이 느려집니다. 그

래서 외부에 저장되는 LOB가 있는 테이블에서는 select * 을 더더욱 사용 하시면
안되겠죠.

결론적으로 MAX를 사용하므로서 Large-Object를 기존 TEXT와 같이 취급하지 않고, 일반 데이터형과 같이 취급할 수 있게 해서, 일반적인 SQL 작

업에서 예외적으로 취급할 필요가 없어졌습니다.

먼저 문자열 조작으로 엄청 긴 문자를 만들어서 넣고,

INSERT INTO dbo.LargeDataType(LargeColumn)
SELECT  'There is lots of data in this row ' + REPLICATE(CAST('x' AS VARCHAR(MAX)), 100000)

특정 단어를 변경해 보겠습니다. TEXT형 컬럼에서는 UPDATETEXT를 사용했지만, 여기서는 일반적인 수형과 똑같이 작업할 수 있습니다.

UPDATE dbo.LargeDataType
SET LargeColumn = REPLACE(LargeColumn, 'lots', 'lots and lots')
SELECT SUBSTRING(LargeColumn, 10, 4) 
FROM dbo.LargeDataType