SQL2008의 새로운 기능 - 필터링된 인덱스

참고 링크
Filtered Index Design Guidelines
http://msdn.microsoft.com/en-us/library/cc280372.aspx
SQL Server Filtered Indexes - What They Are, How to Use and Performance Advantages 

• http://www.mssqltips.com/tip.asp?tip=1785

• http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-drawback-of-using-filtered-indexes.aspx 로컬 변수의 경우 필터드 인덱스를 선택하지 않는다. 필터드 인덱스를 탈 수 있도록 하기 위해서는 with option(recompile)을 해줘야 함.
  
  http://www.brentozar.com/archive/2013/11/filtered-indexes-and-dynamic-sql/  ad-hoc 쿼리의 경우 이슈가 생길 수 있음.

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

 

참고링크
Filtered Index Design Guidelines : http://msdn.microsoft.com/en-us/library/cc280372.aspx
추후 재 정리하도록 하겠습니다^^

USE tableDB

go;

 

CREATE TABLE DBO.T_FILTER_INDEX

(

             a INT

             , b INT

)

GO

 

DROP INDEX T_FILTER_INDEX.CL_T_FILTER_INDEX_a

GO

 

-- 클러스터드인덱스에는생성이안된다.

--CREATE CLUSTERED INDEX CL_T_FILTER_INDEX_a

--ON dbo.T_FILTER_INDEX( a )

--WHERE a IS NOT NULL;

--GO

 

-- NC만생성이가능하구낭??

CREATE INDEX NC_T_FILTER_INDEX_a

ON dbo.T_FILTER_INDEX( a )

WHERE a IS NOT NULL;

GO

 

 

SET NOCOUNT ON

GO

 

-- 10000 번반복하다.

INSERT INTO dbo.T_FILTER_INDEX(a, b) VALUES(NULL, NULL)

GO 10000

 

DECLARE @i INT = 0;

WHILE @i < 40000

BEGIN

             INSERT INTO dbo.T_FILTER_INDEX(a, b) VALUES(@i, @i)     

 

             SET @i+=1;

END

 

 

EXEC sp_spaceused 'T_FILTER_INDEX'

GO

 

--name rows      reserved             data       index_size          unused

--T_FILTER_INDEX           50706               2504 KB 1656 KB 744 KB   104 KB

 

DBCC SHOW_STATISTICS('T_FILTER_INDEX', 'NC_T_FILTER_INDEX_a')

GO

 

SELECT COUNT(*)

FROM T_FILTER_INDEX

WHERE a IS NOT NULL

 

--Name                                                                                                                             Updated              Rows                 Rows Sampled         Steps  Density       Average key length String Index Filter Expression                                                                                                                                                                                                                                                Unfiltered Rows

---------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------

--NC_T_FILTER_INDEX_a                                                                                                              01 13 2010  3:55PM   40000                40000                3      1             4                  NO           ([a] IS NOT NULL)                                                                                                                                                                                                                                                50706

 

--All density   Average Length Columns

--------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--2.5E-05       4              a

 

--RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS

-------------- ------------- ------------- -------------------- --------------

--0            0             1             0                    1      

--39998        39997         1             39997                1

--39999        0             1             0                    1

 

 

 

 

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

사용예,,>>

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 옵션이 허용되지 않습니다.

필터링 인덱스 >>

>> 장점 

인덱스 사이즈가 줄겠죠??  NULL Value 또는 특정 Value로 되어 있는 부분에 대한 인덱스 생성 하기 두려웠는데~ 인덱스 필터링으로 가능해짐..