I am receiving error 666:

the maximum system-generated unique value for a duplicate group was exceeded.

I am experiencing this on an Azure SQL Managed Instance.

However, I shouldn't be getting this error. The index in question is a clustered primary key on a bigint identity column. So there are no duplicates, and should be no uniquifier.

The table starts empty with the current ident of zero. I am attempting to insert 2.9 billion records, letting SQL Server assign the identity key. After receiving error 666, the current ident is 2147483648.

This makes no sense to me, as uniquifier is supposedly only used on non-unique clustered indexes that contain duplicate values. My table does not meet those conditions.

Does anyone have any ideas?

The records are inserted using an INSERT INTO SELECT FROM ORDER BY query. All columns are specified other than the identity column itself. I am absolutely certain the data type of the identity column is BIGINT.

The source table is a heap table with no identity or clustered indexes, so the bigint column in question does not exist there. The destination table has a duplicate definition to the source table, other than having the bigint identity column / clustered primary key added.

I am inserting all records from the source to the destination table, specifying all columns from the source table in the column list / select list for the insert. The bigint column is not specified in the insert because it is an identity column on the destination side only.

I had a thought to pre-populate my source heap table with a unique id column first (like row number), enable identity_insert on the destination table and try batched inserts of 100m at a time. Could that work?

Every time this query fails it takes 15 hours to roll back.

There is an NCI on the heap table in the desired insert order, but it doesn't include all columns so there are bookmark lookups happening. If I do an uncommitted read against the destination table during the load, I can see records, with an increasing identity.

Last execution plan I saw was a table scan followed by a sort.

  • 5
    Can you share the table definitions and the estimated plan for the query that generates the error? Dec 31 '18 at 4:30
  • 2
    Just checked and on 2017 RTM I had no issue inserting 4 billion rows to a bigint table with an identity column as below. CREATE TABLE T ( Id BIGINT IDENTITY PRIMARY KEY, X TINYINT ); WITH T1(N) AS (SELECT 1 UNION ALL SELECT 1), -- 2 T2(N) AS (SELECT 1 FROM T1 A, T1 B), -- 4 T3(N) AS (SELECT 1 FROM T2 A, T2 B), -- 16 T4(N) AS (SELECT 1 FROM T3 A, T3 B), -- 256 T5(N) AS (SELECT 1 FROM T4 A, T4 B), -- 65,536 T6(N) AS (SELECT 1 FROM T5 A, T5 B) -- 4,294,967,296 INSERT INTO T WITH (TABLOCKX) SELECT 1 FROM T6 Dec 31 '18 at 23:34

I was able to resolve this by doing a batched insert of 100m records at a time. The insert completed successfully without error, and I was able to insert 2.88bn records.

The other solution I tested was to insert the records into a table with an identity column but no clustered index, then add the clustered primary key afterward. That also worked.

Not sure why this was occurring inserting into a clustered table. I only tested this on Azure SQL MI, not our on-premise SQL Server 2017. So I don't know if there is any difference there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.