Creating Indexes on Views
Indexes can be defined on views. Indexed views are a method of storing the result set of the view in the database, thereby reducing the overhead of dynamically building the result set. An indexed view also automatically reflects modifications made to the data in the base tables after the index is created.
Note You can create indexed views only if you install Microsoft® SQL Server™ 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.
Indexed views include these benefits:
- Indexed views are implemented through simple syntax extensions to the CREATE INDEX and CREATE VIEW statements.
- The data in indexed views are updated automatically as data in the base tables are updated, in much the same way that the keys in indexes on base tables are updated automatically. You do not need to synchronize the contents of the indexed view with the data in the underlying base tables.
- Indexed views are considered by the SQL Server optimizer without the need to specify special hints in queries. The optimizer considers the indexed view even if a query does not directly reference the view in the FROM clause by trying to match the query plan generated for the view with some portion of the plan generated for the query.
- To introduce indexed views in an existing database, you have to issue only the relevant CREATE VIEW and CREATE INDEX statements. Few changes have to be made to application code for SQL Server to take advantage of any indexes on views.
The Index Tuning Wizard recommends indexed views in addition to recommending indexes on base tables. Using the wizard greatly enhances an administrator's ability to determine the combination of indexes and indexed views that optimize the performance of the typical mix of queries executed against a database.
Indexed views can be more complex to maintain than indexes based on base tables. You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications.