When is indexed view updated

UselessView Id. SET u. Leave new Guillermo. Good to know! How do you usually come across these finds? Erik Darling. I get really bored waiting for new Faith No More albums to come out.

Hi Erik, great write up. Leave a Reply Cancel reply Your email address will not be published. I love teaching, travel, cars, and laughing.

The normal facilities provided by the query optimizer are employed to simplify and optimize the standard maintenance shape as appropriate. We will now turn to an example to help bring these concepts together. The simplest example of an indexed view maintenance plan for this setup occurs when we add a single row to the base table:. Believe it or not, the single-row base table insert execution plan discussed above was subject to a number of simplifications.

Although some possible further optimizations were missed as noted , the query optimizer still managed to remove some operations from the general indexed view maintenance template, and reduce the complexity of others. Several of these optimizations were allowed because we were inserting just a single row, but others were enabled because the optimizer was able to see the literal values being added to the base table.

For example, the optimizer could see that the group value inserted would pass the predicate in the WHERE clause of the view. If we now insert two rows, with the values "hidden" in local variables, we get a slightly more complex plan:. The Stream Aggregate groups by the view key, and computes the sum and count aggregates per group:. The Compute Scalar identifies the action to take per row insert or update in this case , and computes the value to insert or update in the view:. The action code is given an expression label of [Act1xxx].

Valid values are 1 for an update, 3 for a delete, and 4 for an insert. This action expression results in an insert code 4 if no matching row was found in the view i. If a matching row was found, the action code is 1 update. In a plan that could result in deletions from the view, the Compute Scalar would detect when this value would become zero no rows for the current group and generate a delete action code 3. The remaining expressions maintain the sum and count aggregates in the view. Notice though that the expression labels [Expr] and [Expr] are not new; they refer to the labels created by the Stream Aggregate.

The logic is straightforward: if a matching row was not found, the new value to insert is just the value computed at the aggregate. If a matching row in the view was found, the updated value is the current value in the row plus the increment computed by the aggregate. So far we have only looked at inserts to the base table.

The execution plans for a deletion are very similar, with just a few minor differences in the detailed calculations. This next example therefore moves on to look at the maintenance plan for a base table update:. As before, this query uses variables to hide literal values from the optimizer, preventing some simplifications from being applied. It is also careful to update two separate groups, preventing optimizations that can be applied when the optimizer knows only a single group a single row of the indexed view will be affected.

The annotated execution plan for the update query is below:. I was expecting to be able to truncate directly an indexed view, and that statement would truncate the first table of the query or the last right join if there were any. Your email address will not be published. Don't subscribe All Replies to my comments Notify me of followup comments via e-mail. You can also subscribe without commenting.

Post Comment. Want to advertise here and reach my savvy readers? Indexing , SQL Server. Security: we create views so that a user can read specific columns out of certain tables, but not all the data.

You Can… The view definition can reference one or more tables in the same database. Leave new Denis Gobo. Jason Kyle. Dev Nambi. Ed Avis. Noel McKinney. I feel this article does not add value over what is already on the web about indexed views. Great follow up post on your Pass Summit session. Indexed views are maintained automatically as part of the queries that affect the base tables on which they are based.

This is why there are so many restrictions on what you can have in an indexed view - the restrictions exist so that the view can be updated just based on the rows affected in the base table, rather than potentially having to re-scan the entire table s to determine what rows the view should now contain.

Where as you can see, the plan includes an insert into the index I on view V. The above was done on a newer version of SQL Server than I don't keep unsupported versions lurking around , but it is the way these have always worked. Even back in the version of the documentation , this restriction is mentioned:. After the clustered index is created, any connection attempting to modify the base data for the view must also have the same option settings required to create the index.

If the view was only updated when it was accessed, there would be no need for this restriction to exist. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. When is an indexed view updated?