The Best Views for Indexes

Not all applications and queries will benefit from SQL Server 2000's indexed views. As with ordinary indexes, you reap no benefits from creating indexes that aren't helpful or that your queries don't use. The penalty for building unused indexed views is the cost of additional disk space and extra maintenance when you modify data in base tables. However, when your applications and queries use the precomputed results stored in indexed views, you can see significant (orders of magnitude) performance improvements.

Data-mart, data-mining, and decision-support applications will typically benefit most from indexed views. Queries that stand to reap the most performance gains from using indexed views contain the following:

In contrast, online transaction processing (OLTP) systems that perform many write operations on somewhat random rows won't be able to use indexed views. Databases with heavy update activity also probably won't be able to take advantage of indexed views because it's unlikely that the updates will affect the same set of rows in the view. In addition, a view that is just a subset of rows or columns, without additional aggregation or computation, isn't a good candidate for an index.