포괄 열이 있는 인덱스

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

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

예를 들어 AdventureWorks 예제 데이터베이스의 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 AdventureWorks;
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 AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

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

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

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

성능 고려 사항

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

• 인덱스 행을 줄이면 한 페이지에 표시됩니다. 이로 인해 I/O가 증가되고 캐시 효율성이 떨어집니다.

• 인덱스를 저장할 디스크 공간이 더 필요합니다. 특히 varchar(max), nvarchar(max), varbinary(max) 또는 xml 데이터 형식을 키가 아닌 인덱스 열로 추가하면 상당히 많은 디스크 공간이 필요할 수 있습니다. 이는 열 값이 인덱스 리프 수준으로 복사되기 때문입니다. 따라서 열 값은 인덱스 및 기본 테이블 모두에 존재합니다.

• 인덱스 유지 관리를 위해 기본 테이블 또는 인덱싱된 뷰에 대해 수정, 삽입, 업데이트 또는 삭제하는 시간이 늘어납니다.

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

참고 항목

개념