Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be higher than the cost of maintaining a table index. If the underlying data is updated frequently, then the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.
Indexed views improve the performance of these types of queries:
- Joins and aggregations that process many rows.
- Join and aggregation operations that are frequently performed by many queries.
- Views can be used to partition data across multiple databases or instances of Microsoft® SQL Server™ 2000.
- Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view.
