MERGE fails with a duplicate key error when using DELETE and INSERT actions
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.
Only on versions 2008-2014
But on 2016 & up are fixed