아티클
04/01/2012

포괄 열이 있는 인덱스

비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가하여 비클러스터형 인덱스의 기능을 확장할 수 있습니다. 키가 아닌 열을 포함하여 여러 쿼리를 처리하는 비클러스터형 인덱스를 만들 수 있습니다. 이는 키가 아닌 열에 다음과 같은 장점이 있기 때문입니다.

키가 아닌 열은 인덱스 키 열로 사용할 수 없는 데이터 형식입니다.
인덱스 키 열의 수 또는 인덱스 키 크기를 계산할 때 데이터베이스 엔진은 키가 아닌 열을 고려하지 않습니다.

쿼리의 모든 열이 키 또는 키가 아닌 열로 인덱스에 포함될 때 키가 아닌 포괄 열이 있는 인덱스는 쿼리 성능을 상당히 향상시킬 수 있습니다. 성능이 향상되는 이유는 쿼리 최적화 프로그램이 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않고 인덱스 내에서 모든 열 값을 찾을 수 있으므로 디스크 I/O 작업을 줄어들기 때문입니다.

[!참고]

인덱스에 쿼리가 참조하는 모든 열이 들어 있으면 일반적으로 이 인덱스는 쿼리를 포함한다고 합니다.

키 열은 모든 수준의 인덱스에 저장되지만 키가 아닌 열은 리프 수준에만 저장됩니다. 인덱스 수준에 대한 자세한 내용은 테이블 및 인덱스 구성을 참조하십시오.

크기 제한을 피하기 위한 포괄 열 사용

비클러스터형 인덱스에 키가 아닌 열을 포함시키면 현재 인덱스 크기 제한인 최대 16개의 키 열 및 최대 900바이트의 인덱스 키 크기가 초과되는 것을 피할 수 있습니다. 인덱스 키 열의 수 또는 인덱스 키 크기를 계산할 때 데이터베이스 엔진은 키가 아닌 열은 계산하지 않습니다.

예를 들어 AdventureWorks2008R2 예제 데이터베이스의 Document 테이블에서 다음 열을 인덱스하려는 경우를 가정해 봅니다.

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

nchar 및 nvarchar 데이터 형식은 각 문자에 대해 2바이트가 필요하므로 위의 3열이 포함된 인덱스는 900바이트의 크기 제한을 10바이트 초과하게 됩니다(455 * 2). CREATE INDEX 문의 INCLUDE 절을 사용하면 인덱스 키는 (Title, Revision)으로 정의되고 FileName은 키가 아닌 열로 정의됩니다. 이 방법으로 인덱스 키 크기는 110바이트(55 * 2)가 되지만 인덱스는 필요한 모든 열을 포함합니다. 다음 문은 이와 같은 인덱스를 만듭니다.

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Document_Title 
ON Production.Document (Title, Revision) 
INCLUDE (FileName);

포괄 열이 있는 인덱스 지침

포괄 열이 있는 비클러스터형 인덱스를 디자인하는 경우 다음 지침을 고려합니다.

키가 아닌 열은 CREATE INDEX 문의 INCLUDE 절에서 정의됩니다.
키가 아닌 열은 테이블 또는 인덱싱된 뷰의 비클러스터형 인덱스에서만 정의될 수 있습니다.
text, ntext 및 image를 제외한 모든 데이터 형식을 사용할 수 있습니다.
결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. 자세한 내용은 계산 열에 인덱스 만들기를 참조하십시오.
키 열과 마찬가지로 image, ntext 및 text 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 키가 아닌 인덱스 열로 허용되는 동안 키가 아닌 포괄 열이 될 수 있습니다.
열 이름은 INCLUDE 목록 및 키 열 목록 모두에서 지정될 수 없습니다.
열 이름은 INCLUDE 목록에서 반복될 수 없습니다.

열 크기 지침

적어도 하나의 키 열을 정의해야 합니다. 키가 아닌 열의 최대 수는 1023입니다. 이 값은 테이블 열의 최대 수보다 1이 적습니다.
키가 아닌 열을 제외한 인덱스 키 열은 최대 16개의 키 열 및 최대 900바이트의 전체 인덱스 키 크기인 기존 인덱스 크기 제한을 따라야 합니다.
키가 아닌 모든 열의 전체 크기는 INCLUDE 절에 지정된 열의 크기에 의해서만 제한됩니다. 예를 들어 varchar(max) 열은 2GB로 제한됩니다.

열 수정 지침

포괄 열로 정의된 테이블 열을 수정하는 경우 다음 제한 사항이 적용됩니다.

인덱스를 먼저 삭제하지 않으면 키가 아닌 열을 테이블에서 삭제할 수 없습니다.
다음 경우를 제외하고 키가 아닌 열은 변경할 수 없습니다.
- 열의 Null 허용 여부를 NOT NULL에서 NULL로 변경합니다.
- varchar, nvarchar 또는 varbinary 열의 길이를 늘립니다.
  
  [!참고]
  
  이러한 열 수정 제한도 인덱스 키 열에 적용됩니다.

디자인 권장 구성

검색 및 조회에 사용된 열만 키 열이 되도록 인덱스 키 크기가 큰 비클러스터형 인덱스를 다시 디자인합니다. 쿼리를 포함한 다른 모든 열을 키가 아닌 포괄 열로 만듭니다. 이 방법을 통해 쿼리를 포함하는 데 필요한 모든 열을 가지게 되지만 인덱스 키 자체는 작으며 효과적입니다.

예를 들어 다음 쿼리를 포함하는 인덱스를 디자인하려는 경우를 가정해 봅니다.

USE AdventureWorks2008R2;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

쿼리를 포함하려면 인덱스에서 각 열을 정의해야 합니다. 모든 열을 키 열로 정의할 수 있지만 키 크기는 334바이트가 됩니다. 실제 검색 조건으로 사용된 유일한 열은 길이가 30바이트인 PostalCode 열이므로 더 나은 인덱스 디자인은 PostalCode를 키 열로 정의하고 다른 모든 열을 키가 아닌 열로 포함시킵니다.

다음 문은 포괄 열이 있는 인덱스를 만들어 쿼리를 포함합니다.

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

성능 고려 사항

불필요한 열은 추가하지 마십시오. 키 또는 키가 아닌 인덱스 열을 너무 많이 추가하면 다음과 같이 성능에 영향을 줍니다.

인덱스 행을 줄이면 한 페이지에 표시됩니다. 이로 인해 I/O가 증가되고 캐시 효율성이 떨어집니다.
인덱스를 저장할 디스크 공간이 더 필요합니다. 특히 varchar(max), nvarchar(max), varbinary(max) 또는 xml 데이터 형식을 키가 아닌 인덱스 열로 추가하면 상당히 많은 디스크 공간이 필요할 수 있습니다. 이는 열 값이 인덱스 리프 수준으로 복사되기 때문입니다. 따라서 열 값은 인덱스 및 기본 테이블 모두에 존재합니다.
인덱스 유지 관리를 위해 기본 테이블 또는 인덱싱된 뷰에 대해 수정, 삽입, 업데이트 또는 삭제하는 시간이 늘어납니다.

데이터를 수정해야 할지, 디스크 공간을 추가할지 결정할 때 성능에 미치는 영향과 쿼리 성능 향상 중 어느 것이 더 중요한지를 결정해야 합니다. 쿼리 성능을 평가하는 방법은 쿼리 튜닝을 참조하십시오.