Understanding Indexed Views

Views are virtual tables whose contents are defined by a query. The query may reference tables and views from one or more databases.

Note:

There are different editions of Microsoft SQL Server. Indexed views and summary tables for Microsoft SQL Server are supported only for Microsoft SQL Server Enterprise Editions.

Microsoft SQL Server provides indexed views to optimize the performance of your database. When the queries need to aggregate a lot of data from different tables and involve complex processing, then the performance of the system gets impacted.

If the view is referenced frequently and involves complex queries, create a unique clustered index on a view. When a unique clustered index is created on a view, it is stored in the database just like a table with a clustered index. Queries use the clustered index while retrieving data from a view. The efficiency of retrieving the data is improved.

Clustered indexes sort the data included in the view based on their key value. The key values are the columns included in the index definition. There can only be one clustered index per view, because the data rows themselves can be sorted in only one order.

Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without being re-coded.

For more information on indexed views see the Microsoft Developer Network website.