Merge with Unique filtered index

Question

So I've discovered a strange SQL Server behavior today.

Suppose I have a table like this, id is primary key

╔════╦══════╦════════╗
║ id ║ name ║ active ║
╠════╬══════╬════════╣
║  1 ║ a    ║      0 ║
║  2 ║ a    ║      1 ║
╚════╩══════╩════════╝

And suppose I have a filtered unique index on name where active = 1. Now, I just want to switch active for rows, set first row inactive and set second row active. When I try to do update it like

update Table1 set 
    active = n.active
from Table1 as t
inner join (values (1, 1), (2, 0)) as n(id, active) on n.id = t.id

it works fine. But if I try to do merge:

merge Table1 as t
using (values (1, 1), (2, 0)) as n(id, active) on n.id = t.id
when matched then
    update set active = n.active;

if failed with error Cannot insert duplicate key row in object 'dbo.Table1' with unique index 'ix_Table1'. The duplicate key value is (a).

Even stranger, if I have table like this (first row have active = 1 and second row have active = 0):

╔════╦══════╦════════╗
║ id ║ name ║ active ║
╠════╬══════╬════════╣
║  1 ║ a    ║      1 ║
║  2 ║ a    ║      0 ║
╚════╩══════╩════════╝

and merge it like this:

merge Table1 as t
using (values (1, 0), (2, 1)) as n(id, active) on n.id = t.id
when matched then
    update set active = n.active;

It works fine again. So it really looks like merge does updates row by row and checking indexe after each row. I've checked unique constraints, unique indexes without filter, it' all work ok. It only failes when I combine merge and filtered index.

So the question is - is it a bug and if it is, what's the best workaround for this?

You can try it on sql fiddle demo.

Answer 1

I've found this article on sqlblog.com - MERGE Bug with Filtered Indexes, it's written by Paul White, dated 2012.

He gave a couple of workarounds:

• Adding all columns referenced in the filtered index’s WHERE clause to the index key (INCLUDE is not sufficient); or
• Executing the query with trace flag 8790 set e.g. OPTION (QUERYTRACEON 8790).

After a bit of research I've found that if I add primary key column into update, it works ok, so the query becomes:

merge Table1 as t
using (values (1, 1), (2, 0)) as n(id, active) on n.id = t.id
when matched then
    update set active = n.active, id = n.id;

I think that it's also possible to add column from updted index, but haven't tested it yet.

sql fiddle demo