Friday, December 25, 2009

SQL Server Error 666: The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID...

Recently while running a huge insert query I got the following error:

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 422214343852032. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

The query was joining 2 huge tables (each having more than 3 billion rows) and then inserting the result in a third table.

The structure of the query was as follows:

insert into table_archive with(TABLOCK) (id, rid, mid, uid, mDate, result, oDate, bid, jid)
select mtrid as id, rid, mid, uid, mDate, result, oDate, bid, jid
from (
select  rid, mid, uid, mDate, result, oDate, bid, jiud, ROW_NUMBER() over(order by jid, mid, rid) as archiveRow
from dbo.table1 with(nolock)
) archiveTable join
(
select mtrid, ROW_NUMBER() over(order by mtrid) as Idrow
from archive_temp.dbo.billionNumbers
) idtable
on idtable.Idrow = archiveTable.archiveRow

After searching the net I found that this issue occurs when the query execution plan has a Table Spool operator. The Table Spool operator creates a unique clustered index on a column when the execution plan is generated for the query. When the number of the input rows exceeds 2,147,483,648, the unique identifier for the clustered index runs out. Therefore, you receive the error message that states that duplicate rows cannot be inserted.

There are couple of things you can try:

1. Install the HotFix that Microsoft has released a HotFix for this issue in SQL Server 2005. The HotFix is available from at this Microsoft Knowledge Base article.
2. Try to use some query hints or index optimizer hints to rewrite the query. You may find a way to avoid the Table Spool operator. In my case I tried using the query hint Option (Hash Join) and this worked for me. When using this query hint in the execution plan I did not get a Table Spool - so I went ahead and tried it and it worked. My final query was:

insert into table_archive with(TABLOCK) (id, rid, mid, uid, mDate, result, oDate, bid, jid)
select mtrid as id, rid, mid, uid, mDate, result, oDate, bid, jid
from (
select  rid, mid, uid, mDate, result, oDate, bid, jiud, ROW_NUMBER() over(order by jid, mid, rid) as archiveRow
from dbo.table1 with(nolock)
) archiveTable join
(
select mtrid, ROW_NUMBER() over(order by mtrid) as Idrow
from archive_temp.dbo.billionNumbers
) idtable
on idtable.Idrow = archiveTable.archiveRow
Option (Hash Join)

In a different scenario, you may have to use a different query hint. If you have used a diff. query hint that worked in your case, do let me know.