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
Returns:
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.
{ 6 comments… read them below or add one }
hahahahaha… someone wastes over 10 years (it takes a long time to count to a billion, aka 80 years, and a very long time to type that much I imagine) just to send you an error message.
I have seen a foum post where somone cot that error.
I don't want to know what they were doing
Ha. I knew you could get the error but I never got bored enough to setup the table/loop (and then let the database grow and grow…) to generate it but, I figured someone probably had. It's totally funny that the error is 666. Thanks for the post Gail.
Cheers,
kt
Shoot – meant to say thanks to Rob as well!
Cheers,
kt
Very interesting Rob,
Thanks for sharing it.
Regards,
Pinal
I managed to get this error because i forgot to explicitlly uniquefy my Cluster Key then when i UNION 48 tables of 78 million each, it managed to happen