How does SQL Server update indexed views?

Every time you insert, update, or delete data in the base table(s) that the indexed view references, the database engine will automatically update the indexed view to reflect the changes. However, I'm not quite sure how the database engine updates the indexed view. Does it completely rerun the query in the view, or does it use a more efficient method to only update the corresponding part?

SQL Server does not completely rebuild the view after every change. That would be grossly inefficient. Instead, indexed views are maintained incrementally using delta algebra. The query processor determines the net effect of the base table changes to the view and applies only those changes necessary to bring the view up to date.

In simple cases, it can calculate the necessary deltas from just the base table changes and the data currently stored in the view. Where the view definition contains joins, the indexed view maintenance portion of the execution plan will need to access the joined tables as well, but this can usually be performed efficiently, given appropriate base table indexes.

For more details see:

• Conor vs Indexed View Updates by Conor Cunningham (Microsoft)

• Indexed Views And Data Modifications by Erik Darling

• Indexed View Maintenance in Execution Plans by me

StackExchange Database Administrators Q#323466, answer score: 10