Home Dashboard Directory Help
Search

MERGE Incorrectly Reports Unique Key Violations by Paul White NZ


Status: 

Active


19
0
Sign in
to vote
Type: Bug
ID: 773895
Opened: 12/9/2012 10:15:33 AM
Access Restriction: Public
0
Workaround(s)
view
8
User(s) can reproduce this bug

Description

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 8670 (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 http://sqlblog.com/blogs/paul_white/archive/2012/12/10/merge-bug-with-filtered-indexes.aspx
Details
Sign in to post a comment.
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 Microsoft 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
Sign in to post a workaround.