SQL: Does an indexed view always get updated for base table updates?

I had a forum question recently that asked if indexed views get updated when the base table gets updated, but the columns that are part of the view are unchanged.

The easiest way to check this is to try it. Let's start with this code to create a table and put some data in it:

Then we'll create a schema bound view on the table, and a clustered index on the view:

Finally, we'll try both types of updates:

The first updates only a column that's not part of the indexed view. The second updates a column that is part of it.

And, as expected, the SQL Server team was mighty clever, and it works exactly as you'd expect:

The first update only touches the base table. The second touches both.

 

Leave a Reply

Your email address will not be published.