Found prove that SQL Server is the Devils Engine - ERROR 666

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 1234567890.
Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

It all started a few hours ago with the discussion on how Sql Server uses uniqueifiers to handle none unique clustered index keys.
After consulting SQL Server Internals books the answer was that SQL Server adds 4 extra bytes per row. So naturally the next question came up: signed or unsigned integer in the 4 bytes... No answer to that question so a test was mandadory

I will not show the source because nobody should do that. But i will dicribe the process: Create a table with one tinyint column
create a clusterd index on the column (do not specify unique). Insert a row with the value 1. Repeat the last step 214783647 times and enjoy the error message above and it's a signed int...

The uniqueifier is per value so even after you could no longer insert a value of 1 into the table you could insert 2 billion and change records with the values 2 and so on.

  • 5 stars
    Comment from: Frank Kalis
    2011-08-09 @ 07:53:17

    Brilliant! I like that developer's humour... :-)

Leave a comment

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)