Joe Torre - Monday, May 15, 2017 6:02 PM
All clustered indexes are unique. That is a true statement. While you don't have to create a clustered index using the UNIQUE key word to establish that property, internally SQL Server makes that clustered index unique by the addition of a uniquifier. You can even see it in action if you use DBCC PAGE. The clustered index is unique because the data is stored at the leaf level of the clustered index and there must be a way to uniquely identify each and every row. Another aspect of the need for uniquely identifying the row comes from the fact that the nonclustered indexes refer to the clustered index key for finding the data (key lookup).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning