Sql server updating data in a view
Let’s see what happens, however, if we turn our standard view into an indexed view.
Before we start, I should mention that there are a host of requirements attached to the creation of indexed views, in any SQL Server Edition.
In other words, when we query a simple view, the optimizer still has to access all of the underlying tables and perform the necessary s and aggregations.
It derives cardinality estimations, and hence the query plan, from statistics associated with those tables.
We can use views to aggregate data in a meaningful way.
Once we create an indexed view, every time we modify data in the underlying tables then not only must SQL Server maintain the index entries on those tables, but also the index entries on the view. In addition, they also have the potential to cause other issues.These examples assume you’re running SQL Server Enterprise Edition, which will automatically consider indexes on a view when creating a query execution plan, whereas SQL Server Standard Edition won’t; you’ll need to use the clause of any query you wish to use the view (more on this shortly).When we re-run the query from Listing 3, we get the same result set, but the execution plan, shown in Figure 4, looks very different.Not only is this a good practice, it’s also a requirement when creating an indexed view (we’ll discuss further requirements as we progress).
Let’s assume that many applications need to run queries like this, joining the same tables, and referencing the same columns in various combinations.
output (Figure 2) shows that SQL Server performed 2,172 logical reads against the five base tables.