Incorrect "Duplicate key" error with unique filtered index - by Razvan Socol

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 462042 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/30/2009 3:56:46 AM
Access Restriction Public


When updating a table with a unique filtered, the "Cannot insert duplicate key row" error appears, even if the final state of the updated table does not contain duplicates. In this case, SQL Server behaves as if the rows were updated one at a time, instead of behaving as if the rows were logically updated simultaneously (like the SQL standard assumes).

In a similar case involving normal indexes, the error is avoided by splitting the index update operation in two operations (delete and insert), which is what it should be done here, too.

Razvan Socol
SQL Server MVP
Sign in to post a comment.
Posted by mtf30rob on 12/12/2012 at 8:32 AM
I have this problem with SQL Server 2008 R2 64bit (SP1 and SP2). I thought it was because I was using SP1 but after upgrading to SP2 I am still getting the issue. My current build is:Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

Where I am seeing the issue and where it is causing me major problems is:
1)    I created a unique filtered index on a nullable column that used in a foreign key relationship
2)    I then “Design” the table in management studio and add a new column.
3)    Click save in the designer window and I get the “Duplicate Key” error

From looking at the change script that management studio generates it is dropping the index and recreating it once the new table has been built. However when recreating the index the filter is missing from the create index syntax which makes the action fail because I have null values in this column in my table. This is annoying but not massively damaging.

What IS massively damaging is that the action is not rolled back correctly and all of the foreign keys on the table are dropped and have to be manually recreated.

The syntax for creating the index is:

Is there a fix for this as it is a massive problem and is stopping me use unique filtered indexes?
Posted by Razvan Socol on 11/13/2010 at 10:46 AM
The problem is fixed in SQL Server 2008 SP2 (build 10.0.4000), but is not fixed in SQL Server 2008 R2 CU4 (build 10.50.1746).
Posted by rlynn on 8/5/2010 at 10:04 AM
Has this problem been corrected? If so, where can I get exactly what fix? (relatively urgent)
Posted by Mohammed [MSFT] on 6/1/2010 at 10:50 AM
Dear Customer,

Thank you for reporting this issue. This issue has been fixed and is scheduled to be released in an upcoming SQL Server 2008 PCU (Public Cumulative Update).

Posted by Microsoft on 6/3/2009 at 4:05 PM
Dear Customer,

Thanks for reporting this issue to us. I can confirm it is an product issue, and we are currently expecting to fix it in SQL Server 2008 Service Pack 2.

Yavor Angelov
Posted by Razvan Socol on 5/31/2009 at 10:41 AM
Erland Sommarskog observed that the split operator is used correctly when the indexed column is changed, but the bug is that the split operator is not used when columns in the filter condition are updated.

Considering this, I have added a workaround (which is usually not feasible, but should be known anyway).