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

 

Msg 666, Level 16, State 2, Line 2

The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422920200585216. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

建立table時沒有指定一個Key欄位並且設定為UNIQUE 或 建立Clustered index沒有設定為UNIQUE ，此時SQL Server會自動在這個該table增加一個隱藏的4-byte UNIQUIFIER欄位，當寫入資料有重複時會在UNIQUIFIER欄位給予一個UNIQUE值，該值最高可以給到  2147483648 (21億個)，當重複資料的狀況超過這個數字就會以下錯誤訊息

The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 422468691427328. Dropping and re-creating the index may resolve this; otherwise, use another clustering key

解決方法:

建議的解決方法是，當資料會有重複的狀況，建立TABLE時應該要指定一個Key 欄位並且設定為UNIQUE

Reference:

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

http://sqlserverbuddy.blogspot.com/2020/08/the-maximum-system-generated-unique.html

Uniqueifier considerations and error 666

https://techcommunity.microsoft.com/t5/sql-server-support/uniqueifier-considerations-and-error-666/ba-p/319096

Having fun with maxed out uniqifiers on a non unique clustered index

http://sqlservercode.blogspot.com/2017/06/having-fun-with-maxed-out-uniqifiers-on.html