SQL Server Interview Question #223

What happens when a clustered index key is not unique?

Advanced Indexing Senior Advanced

Detailed Explanation

SQL Server allows a non-unique clustered index. When duplicate clustered-key values exist, SQL Server can add an internal uniqueifier to distinguish rows.

That uniqueifier can increase storage requirements because the clustered row locator is used by nonclustered indexes. If a naturally unique, narrow clustered key is available, it can avoid this overhead, but uniqueness is only one factor in clustered-index design.