9

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?

0

1 Answer 1

10

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.