I am receiving error 666:
the maximum system-generated unique value for a duplicate group was
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
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.
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.