Using Indexed View to improve performance

While traditional methods of using index is extremely important for best possible performance of a query in a SQL Server, indexed view also can play a very important beneficial role for the same purpose.

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly  or the query optimizer can select using the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes.  However the query optimizer considers several conditions to determine if an indexed view can cover the entire query or a portion of it. Couple of vital determining factors are as below:

1. The tables in the query FROM clause must be a superset of the tables in the indexed view  FROM clause.

2. The join conditions in the query must be a superset of the join conditions in the view.

3. The aggregate columns in the query must be derivable from a subset of the aggregate columns in the view.

4. All expressions in the query select list must be derivable from the view select list or from the tables not included in the view definition.

So we can see we do not need to directly use an indexed view to take it’s advantage. Rather SQL Server can itself determine to use a indexed view internally (as a part of execution plan) if it is deemed beneficial. This can be tremedaously beneficial if we can use it intelligently with other traditional approaches of Performance or query tuning.

To design an indexed view is a very complex activity and needs lots of experience. Bad design can even decrease performance of the query (for example, if SQL Server finds that there is a possibility of using several indexed views for a query, it can negatively impact the performance) and also can increase the overhead of SQL Server. So you must be very careful

while designing index view and treat them with similer importance of index.

Personally I prefer to use DTA (Database tuning advisor) as a good tool to identify scope of using indexed view while doing performance tuning of a query or database with a sanity check.

You can refer to http://technet.microsoft.com/en-us/library/cc917715.aspx for more detailed information about indexed view and to learn using this to improve performance of a query.