Sql server updating data in a view

Rated 3.91/5 based on 911 customer reviews

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.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.Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression.They aren't there for reasons of performance, and so indexed views are designed to remedy this shortcoming.

sql server updating data in a view-6

sql server updating data in a view-17

sql server updating data in a view-48

It derives cardinality estimations, and hence the query plan, from statistics associated with those tables.

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.

Figure 3 We see the exact same execution plan, output, and query cost if we run the query in Listing 1 again.

Although the use of the view made writing the query easier, it had no impact on query performance.

Leave a Reply