Index Architecture and Behavior - SQL Server Query Performance Tuning - page 118

WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
WHERE n < 20
)
INSERT INTO dbo.Test1
(C1, C2)
SELECT n,
2
FROM Nums;
CREATE INDEX iTest ON dbo.Test1(C1);
Since the indexed column is narrow (the INT data type is 4 bytes), all the index rows can be accommodated in one
8KB index page. As shown in Figure 8-8 , you can confirm this in the dynamic management views associated with indexes.
Figure 8-8. Number of pages for a narrow, nonclustered index
SELECT i.Name,
i.type_desc,
ddips.page_count,
ddips.record_count,
ddips.index_level
FROM sys.indexes i
JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'dbo.Test1'), NULL,
NULL, 'DETAILED') AS ddips
ON i.index_id = ddips.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Test1');
The sys.indexes system table is stored in each database and contains the basic information on every index
in the database. The dynamic management function, sys.dm_db_index_physical_stats, contains the more detailed
information about the statistics on the index (you'll learn more about this DMF in Chapter 13). To understand
the disadvantage of a wide index key, modify the data type of the indexed column c1 from INT to CHAR(500)
( narrow_ alter.sql in the download).
DROP INDEX dbo.Test1.iTest;
ALTER TABLE dbo.Test1 ALTER COLUMN C1 CHAR(500);
CREATE INDEX iTest ON dbo.Test1(C1);
The width of a column with the INT data type is 4 bytes, and the width of a column with the CHAR(500) data type
is 500 bytes. Because of the large width of the indexed column, two index pages are required to contain all 20 index
rows. You can confirm this in the sys.dm_db_index_physical_stats dynamic management function by running the
query against it again (see Figure 8-9 ).