Indexed views in SQL Server

The indexed view is updated after all triggers on the base tables are executed. You can see that in the query plan. There will be only one clustered index update on the view.

However, you can create INSTEAD OF triggers on the view and then make all your operations on the indexed view. However,there are some restrictions when updating/inserting/deleting through an updateable view. You can read about them here.

Note that views allow only INSTEAD OF triggers, not AFTER too.

The most important thing to know about updating indexed views is that any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain9y ago
The indexed view is updated after all triggers on the base tables are executed. You can see that in the query plan. There will be only one clustered index update on the view.
However, you can create INSTEAD OF triggers on the view and then make all your operations on the indexed view. However,there are some restrictions when updating/inserting/deleting through an updateable view. You can read about them here.
Note that views allow only INSTEAD OF triggers, not AFTER too.
The most important thing to know about updating indexed views is that any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.