MERGE fails with a duplicate key error when using DELETE and INSERT actions - by Itzik Ben-Gan

Status : 

 


11
0
Sign in
to vote
ID 3137805 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 7/14/2017 11:42:00 AM
Access Restriction Public

Description

Similar to bug 773895 (MERGE generating unique key violations in the presence of a unique filtered index) reported by Paul White (for details, see his blog: http://sqlblog.com/blogs/paul_white/archive/2012/12/10/merge-bug-with-filtered-indexes.aspx). Only this bug demonstrates MERGE incorrectly reporting unique key violations with nonfiltered unique indexes, which is a much more common scenario (happens with both clustered and nonclustered indexes). The bug involves a MERGE statement with DELETE and INSERT actions. Transient unique key violations occur when the optimizer incorrectly chooses a narrow index update strategy instead of a wide one to avoid those.
Sign in to post a comment.