DBMS/SQL Server

[SQL Server] Index Depth 확인하기

Denali 2013. 1. 23. 21:32

 

Index Depth 확인하기

 -- 테이블 생성

 CREATE TABLE DBO.TEST_INDEXDEP (

    COL1 CHAR(20)

  , COL2 CHAR(800)

 )

 GO

 

 SET NOCOUNT ON

 DECLARE @I INT = 1

 

 WHILE(@I < 50000)

 BEGIN

       INSERT INTO DBO.TEST_INDEXDEP

       SELECT CAST(@I AS CHAR(20)), CAST(@I AS CHAR(800))

       SET @I = @I + 1

 END

 

 SET NOCOUNT OFF

 

 

 -- 20Byte 인덱스 생성

 CREATE NONCLUSTERED INDEX IDX_COL1 ON DBO.TEST_INDEXDEP(COL1)

 

 -- 800Byte 인덱스 생성

 CREATE NONCLUSTERED INDEX IDX_COL2 ON DBO.TEST_INDEXDEP(COL2)

 

 

 -- 각각의 Index Depth 확인

 SELECT INDEXPROPERTY(OBJECT_ID('TEST_INDEXDEP'), 'IDX_COL1'

                                                , 'INDEXDEPTH')

                                               

 SELECT INDEXPROPERTY(OBJECT_ID('TEST_INDEXDEP'), 'IDX_COL2'

                                                , 'INDEXDEPTH')