인덱스 레벨별 사이즈 비교

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TIndexDep]') AND type in (N'U'))
    DROP TABLE dbo.TIndexDep
GO

CREATE TABLE dbo.TIndexDep
(
    col1    INT,
    col2    CHAR(20),
    col3    CHAR(900),
    col4    CHAR(7000)
)
GO

SET NOCOUNT ON

DECLARE @i INT = 1
WHILE (@i < 500000)
BEGIN
    INSERT INTO dbo.TIndexDep
        SELECT @i, CAST(@i as CHAR(20)), CAST(@i as CHAR(900)), CAST(@i as CHAR(7000))
        SET @i += 1
END
SET NOCOUNT OFF


SELECT * FROM TIndexDep

CREATE NONCLUSTERED INDEX IX_col2 ON dbo.TIndexDep(col2)

CREATE NONCLUSTERED INDEX IX_col3 ON dbo.TIndexDep(col3)



SELECT INDEXPROPERTY(OBJECT_ID('TIndexDep'), 'IX_col2', 'INDEXDEPTH')
SELECT INDEXPROPERTY(OBJECT_ID('TIndexDep'), 'IX_col3', 'INDEXDEPTH')

SELECT index_depth, index_level, record_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TIndexDep'), 2 /* IX_col2 */, NULL, 'DETAILED')

SELECT index_depth, index_level, record_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TIndexDep'), 3 /* IX_col3 */, NULL, 'DETAILED')