MERGE Incorrectly Reports Unique Key Violations - by Paul White NZ

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<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 773895 Comments
Status Closed Workarounds
Type Bug Repros 10
Opened 12/9/2012 10:15:33 AM
Access Restriction Public


A MERGE statement can fail, and incorrectly report a unique key violation when:

*  The target table uses a unique filtered index; and
*  No key column of the filtered index is updated; and
*  A column from the filtering condition is updated; and
*  Transient key violations are possible

The optimizer incorrectly chooses a narrow update plan where a wide plan with split/sort/collapse is required to avoid transient nonclustered index key violations.

Forcing a wide update plan with TF 8790 (or by adding all columns referenced in the index filter predicates to the index *keys*) avoids this problem.

There is a reproduction script in the details section below, and a fuller discussion at
Sign in to post a comment.
Posted by Vaccanoll on 4/23/2015 at 1:42 PM
This is absurd. How can you close something like this as "Won't Fix" and not give an explanation.....

Amateur stuff here folks.
Posted by Newfangled Old-fashioned Stuff on 4/23/2015 at 7:06 AM
Microsoft - please explain WHY this is a "won't fix" bug when there's code already written that already forces a wide update (trace flag 8790) which could be used by the optimizer when it sees this particular combination of column types in a MERGE. You could even check for the conditions, then simply insert the trace flag into the query and re-generate a plan that would work - it's not elegant, but it shouldn't take all that long to implement.

Everyone else: no surprise, but this bug is still present in SQL 2014 build 2480 (X64) as well as SQL 2012 build 5522 (X64).
Posted by Philip Lewis on 2/13/2015 at 6:27 AM
What a waste of my time ... the standard answer is always the same 'BUG ... closed, won't fix'

I honestly don't think they care about product quality, they are obsessed with "new and shiny" functionality.
Posted by Wiggity Grub on 6/4/2014 at 5:00 AM
Four more months... Anything we can do other than avoid using the MERGE functionality?
Posted by DWalker on 2/22/2014 at 1:42 PM
"Microsoft will get back to you." OK, now it's 13 months later. Any word from them?
Posted by Jean-Yves [MSFT] on 1/4/2013 at 4:41 PM
Hi Paul White NZ ,
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 

Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server