Michael J. Swart is one of a kind:
If you define a clustered index that’s not unique, SQL Server will add a hidden 4-byte column called
UNIQUIFIER. You can’t see it directly but it’s there. When you add a row whose key is a duplicate of an existing row, the new row gets a new unique value for it’s uniqueifier. If you add over 2.1 billion rows with the same key, the uniquifier value exceeds the limit and you will see error 666.A while ago, we nearly got into trouble because of a bad choice for clustering key that went undetected for so long.
Click through for a query to see how many clustered indexes need uniquifiers and which have the most duplication of key fields.