SQL Server Uniqueifier Values and the Number of the Beast

February 13, 2009 · 8 comments

Reading Gail Shaw’s post All indexes are unique, reminded me of an interesting piece of trivia I discovered back in SQL Server 7. The error message for running out of uniqueifier values is 666.

SELECT text FROM sys.messages WHERE message_id = 666


The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Here is what SQL Server 2008 Books Online – Clustered Index Design Guidelines says :

If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.

Someone on the SQL Server team definitely had a sense of humor, because to actually see this message, you would have to insert over 4,294,967,296 duplicate entries into your clustered index column. Anyone who would do that is truly evil.

{ 8 comments… read them below or add one }

Leave a Comment