SQL Server Interview Question #227
Why are narrow index keys generally preferred?
Advanced Indexing Senior Advanced
Quick Interview Answer
Narrow keys allow more index entries to fit on each 8 KB page, reducing storage and potentially reducing the number of pages SQL Server must read.
This is particularly important for clustered keys because clustered-key values are included as row locators in nonclustered indexes. A wide clustered key can therefore multiply storage cost across the database.
However, an index key must first support the required access pattern; minimizing width should not destroy useful query ordering or selectivity.
Detailed Explanation
Narrow keys allow more index entries to fit on each 8 KB page, reducing storage and potentially reducing the number of pages SQL Server must read.
This is particularly important for clustered keys because clustered-key values are included as row locators in nonclustered indexes. A wide clustered key can therefore multiply storage cost across the database.
However, an index key must first support the required access pattern; minimizing width should not destroy useful query ordering or selectivity.