Database Reference
In-Depth Information
WITH Nums
AS (SELECT TOP (20)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
)
INSERT INTO dbo.Test1
(C1, C2)
SELECT n,
n + 1
FROM Nums;
CREATE CLUSTERED INDEX iClustered
ON dbo.Test1 (C2);
CREATE NONCLUSTERED INDEX iNonClustered
ON dbo.Test1 (C1);
Since the table has a clustered index, the row locator of the nonclustered index contains the clustered index key
value. Therefore:
•
Width of the nonclustered index row = width of the nonclustered index column + width of the
clustered index column = size of
INT
data type + size of
INT
data type
= 4 bytes + 4 bytes = 8 bytes
With this small size of a nonclustered index row, all the rows can be stored in one index page. You can confirm
this by querying against the index statistics, as shown in Figure
8-19
.
Figure 8-19.
Number of index pages for a narrow index
SELECT i.name,
i.type_desc,
s.page_count,
s.record_count,
s.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 s
ON i.index_id = s.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Test1');
