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.