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.
We’ll also consider the potential pitfalls of which you need to be aware before deciding to implement an indexed view. Unfortunately, however, applications grow more complex as the users demand new features, and so the accompanying queries grow more complex also.
Listing 1 Notice that we use two-part naming for all tables.Indexed views can be a powerful tool, but they are not a ‘free lunch’ and we need to use them with care.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.A well-crafted indexed view can write fewer pages to disk than the underlying tables, meaning fewer pages queries need to read fewer pages to return results. Use the techniques and tips in this article to ensure your views are optimal!Let’s see the impact of our indexed view on query performance.They're great in certain circumstances but they represent a trade-off, and they come with considerable 'small print'. SQL Server views are helpful in many ways, for example in encapsulating complex multi-table query logic, allowing us to simplify client code.Views make queries faster to write, but they don’t improve the underlying query performance.We can use views to aggregate data in a meaningful way.Let’s say we need to run various queries against the database to return information regarding items that customers have purchased.output (Figure 2) shows that SQL Server performed 2,172 logical reads against the five base tables.Figure 2 The execution plan reports the query cost as 6.01323, as shown in Figure 3.