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

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.

{ 8 comments… read them below or add one }

Isaac Lee February 14, 2009 at 2:56 am

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.

Gail February 14, 2009 at 7:54 am

I have seen a foum post where somone cot that error.
I don't want to know what they were doing

Kimberly L. Tripp February 14, 2009 at 10:02 pm

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

Kimberly L. Tripp February 14, 2009 at 10:04 pm

Shoot – meant to say thanks to Rob as well!

Cheers,
kt

Pinal Dave February 25, 2009 at 12:04 am

Very interesting Rob,

Thanks for sharing it.

Regards,
Pinal

Paul November 19, 2010 at 1:16 pm

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

Lonny Niederstadt February 8, 2013 at 12:02 pm

I think SQL Server 2005 kb 937533 is related. Table spool when copying tables might be especially vulnerable.
http://support.microsoft.com/kb/937533
I believe I saw this today when a 9.5 billion row table was being copied from one database to another.

Kevin Boles February 8, 2013 at 1:55 pm

I DID test the uniquefier limit in SQL 2005 (because of the forum thread mentioned IIRC) and found it to be just over 2.0B, NOT the positive limit of an int and not the entire range of values of an int either. BTW, it took me ~16 hours of 500K row inserts per batch to hit the limit!! :-)

Leave a Comment