행 내부 데이터
데이터 행에는 작거나 중간 크기 및 큰 값 데이터 형식(varchar(max), nvarchar(max), varbinary(max) 및 xml)과 LOB(Large Object) 데이터 형식(text, ntext 및 image)이 저장될 수 있습니다. 이러한 동작은 sp_tableoption 시스템 저장 프로시저의 두 가지 옵션을 사용하여 제어할 수 있습니다. 즉, 큰 값 데이터 형식에는 large value types out of row 옵션을 사용하고 큰 개체 데이터 형식에는 text in row 옵션을 사용할 수 있습니다. 이 옵션은 이러한 데이터 형식의 데이터 값을 일반적으로 하나의 단위로 읽거나 쓰는 테이블에 가장 적합하며 테이블을 참조하는 대부분의 문이 이러한 형식의 데이터를 참조합니다. 용도 또는 작업 특성에 따라 행 내부에 데이터를 저장하는 것이 적절하지 않을 수도 있습니다.
.gif "중요 정보") 중요 |
|---|
text in row 옵션은 SQL Server의 이후 버전에서 제거될 예정입니다. 향후 개발 작업에서는 이 옵션을 사용하지 않도록 하고 현재 text in row를 사용하는 응용 프로그램은 수정하십시오. 대량의 데이터는 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식을 사용하여 저장하는 것이 좋습니다. 이러한 데이터 형식의 행 내부 및 행 외부 동작을 제어하려면 large value types out of row 옵션을 사용하십시오. |
text in row 옵션을 On 또는 특정 행 내부 제한으로 설정하지 않은 경우 text, ntext 또는 image 문자열은 데이터 행 외부에 저장되는 큰 문자이거나 이진 문자열(최대 2GB)입니다. 데이터 행에는 내부 포인터로 구성된 트리의 루트 노드를 가리키는 16바이트 텍스트 포인터만 포함됩니다. 이 포인터는 문자열 조각이 저장된 페이지를 매핑합니다. text, ntext 또는 image 문자열의 저장소에 대한 자세한 내용은 텍스트 및 이미지 데이터 사용을 참조하십시오.
LOB 데이터 형식 열이 포함된 테이블에 text in row 옵션을 설정할 수 있습니다. 또한 text in row 옵션 제한을 24바이트부터 7,000바이트까지 지정할 수도 있습니다.
마찬가지로 large value types out of row 옵션을 ON으로 설정하지 않으면 varchar(max), nvarchar(max), varbinary(max) 및 xml 열이 데이터 행 내부에 저장됩니다(가능한 경우). 이 경우 SQL Server 데이터베이스 엔진은 특정 값에 맞추려고 시도하며 그렇지 않은 경우에는 행 외부로 밀어넣습니다. large value types out of row 옵션을 ON으로 설정하면 값이 행 외부에 저장되고 16바이트 텍스트 포인터만 레코드에 저장됩니다.
[!참고]
large value types out of row 옵션을 OFF로 설정하면 큰 값 데이터 형식의 최대 행 내부 저장소는 8,000바이트로 설정됩니다. text in row 옵션과는 달리 테이블의 열에 대해서는 행 내부 제한을 지정할 수 없습니다.
큰 값 데이터 형식 또는 큰 개체 데이터 형식을 데이터 행에 직접 저장하도록 테이블이 구성되어 있는 경우 다음 조건 중 하나에 해당한다면 실제 열 값은 행 내부에 저장됩니다.
문자열의 길이가 text, ntext 및 image 열에 지정된 제한보다 짧은 경우
데이터 행에 문자열을 저장할 충분한 공간이 있는 경우
큰 값 데이터 형식 또는 큰 개체 데이터 형식 열 값이 데이터 행에 저장되면 데이터베이스 엔진은 문자열 또는 이진 문자열을 읽거나 쓰기 위해 개별 페이지나 페이지 집합에 액세스하지 않아도 됩니다. 따라서 행 내부 문자열을 읽고 쓰는 속도가 제한된 크기의 varchar, nvarchar 또는 varbinary 문자열을 읽고 쓰는 속도만큼 빨라집니다. 마찬가지로 값이 행 외부에 저장되면 데이터베이스 엔진이 추가로 페이지를 읽거나 쓰게 됩니다.
큰 개체 데이터 형식에서 문자열이 text in row 옵션 제한이나 행에서 사용할 수 있는 공간 크기보다 긴 경우 일반적으로 포인터 트리의 루트 노드에 저장되던 포인터 집합이 행에 저장됩니다. 다음 조건 중 하나에 해당하면 포인터는 행에 저장됩니다.
포인터를 저장하는 데 필요한 공간 크기가 지정된 text in row 옵션 제한보다 짧은 경우
데이터 행에 포인터를 저장할 충분한 공간이 있는 경우
포인터가 루트 노드에서 행으로 이동하면 데이터베이스 엔진이 루트 노드를 사용하지 않아도 됩니다. 따라서 문자열을 읽거나 쓸 때 페이지를 액세스할 필요가 없으므로 성능이 향상됩니다.
루트 노드가 사용될 때 루트 노드는 LOB 페이지의 문자열 조각 중 하나로 저장되므로 최대 다섯 개의 내부 포인터를 포함할 수 있습니다. 데이터베이스 엔진에서 행 내부 문자열에 대해 다섯 개의 포인터를 저장하려면 행 내부에 72바이트의 공간이 필요합니다. text in row 옵션이 ON으로 설정되거나 large value types out of row 옵션이 OFF로 설정된 경우 행에 포인터를 저장할 공간이 부족하면 데이터베이스 엔진에서 8KB의 페이지를 할당하여 포인터를 저장해야 할 수 있습니다. 값의 데이터 길이가 40,200바이트를 초과하면 주 행에 24바이트만 저장되고 LOB 저장소 공간에 추가 데이터 페이지가 할당되는 지점에서 행 내부 포인터가 5개 이상 필요합니다.
큰 문자열은 행에 저장될 때 가변 길이 문자열과 유사하게 저장됩니다. 데이터베이스 엔진은 열을 크기에 따라 내림차순으로 정렬하고 나머지 열이 데이터 페이지(8K)에 맞을 때까지 값을 행 외부로 밀어넣습니다.
large value types out of row 옵션 설정 및 해제
다음과 같이 sp_tableoption을 사용하여 테이블에 large value types out of row 옵션을 설정할 수 있습니다.
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
OFF로 지정하면 varchar(max), nvarchar(max), varbinary(max) 및 xml 열에 대한 행 내부 제한이 8,000바이트로 설정됩니다. 16바이트 루트 포인터만 행 내부에 저장되고 값은 LOB 저장소 공간에 저장됩니다. 대부분의 문이 큰 값 데이터 형식 열을 참조하지 않는 테이블에 대해서는 이 옵션을 ON으로 설정하는 것이 좋습니다. 이러한 열을 행 외부에 저장하면 각 페이지에 더 많은 행이 들어갈 수 있으므로 테이블을 검색하는 데 필요한 I/O 작업 수가 줄어듭니다.
이 옵션의 값을 OFF로 설정하면 결국 많은 문자열이 행 자체에 저장되어 각 페이지에 맞는 데이터 행의 수가 줄어들 수 있습니다. 테이블을 참조하는 대부분의 문이 varchar(max), nvarchar(max), varbinary(max) 및 xml 열을 액세스하지 않을 때 페이지의 행 수를 줄이면 쿼리를 처리하기 위해 읽어야 하는 페이지 수가 늘어날 수 있습니다. 페이지당 행 수를 줄이면 최적화 프로그램이 사용할 수 있는 인덱스를 찾지 못할 때 검색해야 하는 페이지의 수가 늘어날 수 있습니다.
sp_tableoption을 사용하여 행 외부 옵션을 해제할 수도 있습니다.
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
large value types out of row 옵션 값이 변경되더라도 기존 varchar(max), nvarchar(max), varbinary(max) 및 xml 값은 즉시 변환되지 않습니다. 문자열의 저장소는 다음에 업데이트될 때 변경됩니다. 테이블에 삽입되는 새 값은 적용된 테이블 옵션에 따라 저장됩니다.
특정 테이블의 large value types out of row 옵션 값을 확인하려면 sys.tables 카탈로그 뷰의 large_value_types_out_of_row 열을 쿼리합니다. 테이블의 large value types out of row 옵션이 설정되어 있지 않으면 이 열의 값이 0이고, 큰 값 유형이 행 외부에 저장되면 이 열의 값이 1입니다.
text in row 옵션 설정 및 해제
다음과 같이 sp_tableoption을 사용하여 테이블에 text in row 옵션을 설정할 수 있습니다.
sp_tableoption N'MyTable', 'text in row', 'ON'
또는 데이터 행에 저장될 수 있는 text, ntext 및 image 문자열의 최대 길이 제한을 24바이트에서 7.000바이트까지로 지정할 수도 있습니다.
sp_tableoption N'MyTable', 'text in row', '1000'
특정 제한 대신 ON을 지정하면 기본 제한 값은 256바이트가 됩니다. 이 기본값을 지정하면 text in row 옵션을 사용하여 최대 성능을 얻을 수 있습니다. 일반적으로 값을 72보다 낮게 설정해서도 안 되며 너무 높게 설정해서도 안 됩니다. 특히 대부분의 문이 text, ntext 및 image 열을 참조하지 않거나 text, ntext 및 image 열이 여러 개 있는 테이블의 경우 이러한 사항을 지켜야 합니다.
text in row 제한을 크게 설정하고 많은 문자열이 행에 저장되면 각 페이지에 들어가는 데이터 행의 수를 상당히 줄일 수 있습니다. 테이블을 참조하는 대부분의 문이 text, ntext 또는 image 열을 액세스하지 않을 때 페이지의 행 수를 줄이면 쿼리를 처리하기 위해 읽어야 하는 페이지 수가 늘어날 수 있습니다. 페이지당 행 수를 줄이면 최적화 프로그램이 사용할 수 있는 인덱스를 찾지 못할 때 검색해야 하는 인덱스 및 페이지의 크기가 커질 수 있습니다. text in row 제한의 기본값인 256은 작은 문자열 및 루트 텍스트 포인터를 행에 저장할 수 있을 만큼 충분히 크지만 페이지당 행 수를 줄여 성능에 영향을 미칠 수 있을 만큼 크지는 않습니다.
text in row 옵션은 테이블 데이터 형식의 변수 및 table을 반환하는 사용자 정의 함수에서 반환한 테이블에 대해 자동으로 256으로 설정됩니다. 이 설정은 변경할 수 없습니다.
sp_tableoption을 사용하여 옵션 값을 OFF 또는 0으로 지정하면 이 옵션을 해제할 수 있습니다.
sp_tableoption N'MyTable', 'text in row', 'OFF'
특정 테이블의 text in row 옵션 값을 확인하려면 sys.tables 카탈로그 뷰의 text_in_row_limit 열을 쿼리합니다. 테이블의 text in row 옵션이 설정되어 있지 않으면 이 열의 값이 0이고 행 내부 제한이 설정되어 있으면 이 열의 값이 0보다 큽니다.
text in row 옵션의 사용 효과
text in row 옵션의 효과는 다음과 같습니다.
text in row 옵션을 설정하면 TEXTPTR, READTEXT, UPDATETEXT 또는 WRITETEXT 문을 사용하여 테이블에 저장된 text, ntext 또는 image 값의 일부를 읽거나 수정할 수 있습니다. SELECT 문에서 전체 text, ntext 또는 image 문자열을 읽거나 SUBSTRING 함수를 사용하여 문자열 중 일부를 읽을 수 있습니다. 테이블을 참조하는 모든 INSERT 또는 UPDATE 문에서는 완전한 문자열을 지정해야 하므로 text, ntext 또는 image 문자열의 일부만 수정할 수는 없습니다.
text in row 옵션을 먼저 설정하더라도 기존 text, ntext 또는 image 문자열이 행 내부 문자열로 즉시 변환되지는 않습니다. 문자열은 다음에 업데이트될 경우에만 행 내부 문자열로 변환됩니다. text in row 옵션을 설정한 후에 삽입된 모든 text, ntext 또는 image 문자열은 행 내부 문자열로 삽입됩니다.
text in row 옵션을 해제하는 데는 시간이 오래 걸리며 속도가 느릴 수 있습니다. 테이블이 잠기며 모든 행 내부 text, ntext 및 image 문자열은 일반 text, ntext 및 image 문자열로 변환됩니다. 명령 실행 시 걸리는 시간과 수정된 데이터의 크기는 행 내부 문자열에서 일반 문자열로 변환해야 하는 text, ntext 및 image 문자열의 수에 따라 달라집니다.
text in row 옵션은 text, ntext 및 image 데이터에 대한 액세스 속도를 향상시키는 것 외에는 SQL Server Native Client OLE DB 공급자 또는 SQL Server Native Client ODBC 드라이버의 작업에 영향을 주지 않습니다.
text in row 옵션이 설정된 후에는 dbreadtext 및 dbwritetext와 같은 DB-Library 텍스트 및 이미지 함수를 테이블에 사용할 수 없습니다.