Combine with a unique filtered index

Question

So, today I discovered the strange behavior of SQL Server.

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

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

And let me have filtered unique index on name where active = 1 . Now I just want it to switch active for the rows , set the first row inactive and set the second row active. When I try to 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 great . But if I try to 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 it failed with the error Cannot insert duplicate key row in object 'dbo.Table1' with unique index 'ix_Table1'. The duplicate key value is (a) Cannot insert duplicate key row in object 'dbo.Table1' with unique index 'ix_Table1'. The duplicate key value is (a) .

Even a stranger, if I have such a table (the first row is active = 1, and the second row is active = 0):

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

and combine it as follows:

 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 great again. So it looks like merge is updating line by line and checking the index after each line. I checked unique restrictions, unique indexes without filter, everything works fine. It only fails when I combine a merge and a filtered index.

So the question is, is this a mistake, and if so, what is the best workaround for this?

You can try it on the sql demo script .

Answer 1

I found this article on sqlblog.com - MERGE error with filtered indexes , written by Paul White from 2012.

He gave a couple of workarounds:

• Adding all columns specified in filtered indexes WHERE clause to index key (INCLUDE is not enough); or
• Fulfilling a query with trace flag 8790, for example. OPTION (QUERYTRACEON 8790).

After a little research, I found that if I add the primary key column to the update, it will work fine, so the query will look like this:

 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 it is also possible to add a column from the updated index, but have not verified it yet.

demo sql