Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Merge statement Delete does not update indexed view in all cases

A merge statement that contains both an update clause and a delete clause, does not always update indexed views that refer the table.

Specifically, if the delete clause is the one that is triggered, and the update clause does not update any of the columns that are used by the indexed view, the deleted row is not removed from the indexed view.

The indexed view is therefore logically inconsistent; it contains rows that are no longer part of the source table. An SQL script that demonstrates the bug is included.

I have replicated the bug both on SQL Server 2014 SP2 Cumulative update 10, and on SQL server 2016 express SP1.

10 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Otto van Houten shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base