SQLServer

필터링된 인덱스

하람윤슬아빠 2020. 1. 14. 12:56

테스트 환경

 - OS : Windows 2008 R2 DataCenter

 - DBMS : SQL Server 2012 Enterprise Edition + SP4

 - 참고문서 : https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2012/ms188783%28v%3dsql.110%29

 

내용

 

개요


인덱스를 생성할때 인덱스에 포함될 데이터를 필터 할 수 있습니다.

비 클러스터형 인덱스에만 해당됩니다.

WHERE
인덱스에 포함할 행을 지정하여 필터링된 인덱스를 만듭니다. 필터링된 인덱스는 테이블의 비클러스터형 인덱스여야 합니다. 필터링된 인덱스의 데이터 행에 대한 필터링된 통계를 만듭니다.

필터 조건자는 간단한 비교 논리를 사용하며 계산 열, UDT 열, 공간 데이터 형식 열 또는 HierarchyID 데이터 형식 열을 참조할 수 없습니다. 비교 연산자에는 NULL 리터럴을 사용한 비교를 사용할 수 없습니다. 대신 IS NULL 및 IS NOT NULL 연산자를 사용합니다.

다음은 Production.BillOfMaterials 테이블에 대한 필터 조건자의 몇 가지 예입니다.

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

필터링된 인덱스는 XML 인덱스 및 전체 텍스트 인덱스에는 적용되지 않습니다. UNIQUE 인덱스의 경우에는 선택한 행만 고유 인덱스 값을 가져야 합니다. 필터링된 인덱스에서는 IGNORE_DUP_KEY 옵션이 허용되지 않습니다.

 

예제

 

다음 예에서는 Production.BillOfMaterials 테이블에 필터링된 인덱스를 만듭니다. 필터 조건자는 필터링된 인덱스에 키 열이 아닌 열을 포함할 수 있습니다. 이 예에서 조건자는 EndDate가 NULL이 아닌 행만 선택합니다.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithEndDate'
AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
GO

 

카탈로그뷰

 

sys.indexes 의 has_filter 속성에 필터링 여부 저장됩니다.

1 = 인덱스에 필터가 있고 포함된 모든 행이 필터 정의를 만족합니다.
0 = 인덱스에 필터가 없습니다.