Kendra Little | January 17, 2024
Indexed views are amazing: they’re like an auto-updating table based on the definition of the view. Whenever rows change in the tables defined in the view, those changes are also applied to the indexed view.
Indexed views can do things like show aggregates that are the result of grouping and aggregations, too, which means it can essentially pre-aggregate results for common queries.
Whether you’re human or a marsupial, it’s worth being a little careful, though.
What went wrong
You know how I mentioned indexed views are auto-updating?
Well, creating an indexed view can dramatically slow down modifications (including inserts) to a member table because every modification potentially requires updating the indexed view, and it needs to get that done before committing the modification transaction.
This can be a considerable amount of overhead, especially if it’s not easy to find the rows that need to be modified.
Test modifications to any tables included in an indexed view before calling it all good
In a development environment, test modifications– like an insert –π to a member table. You can even do this within a transaction and roll back the transaction if you don’t want to update the data in your dev environment. See how long it takes.
If it takes much longer than doing that modification before creating the indexed view, look at the execution plan for the modification query for help identifying what has gone wrong.
It’s likely that you need to add a nonclustered rowstore index to prevent the query plan from having to scan an object.
“Eucan’t be too careful!”