Designing an Indexed View
Indexed views improve the performance of some types of queries dramatically.
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 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.
For example, in an online-transaction-processing (OLTP) database that is recording inventories, many queries would be expected to join the Parts, PartSupplier, and Suppliers tables. Although each query that performs this join may not process many rows, the overall join processing of hundreds of thousands of such queries can be significant. Because these relationships are not likely to be updated frequently, the overall performance of the entire system could be improved by defining an indexed view that stores the joined results.
- Decision support workloads.
Analysis systems are characterized by storing summarized, aggregated data that is infrequently updated. Further aggregating the data and joining many rows characterizes many decision support queries.
Indexed views usually do not improve the performance of these types of queries:
- OLTP systems with many writes.
- Databases with many updates.
- Queries that do not involve aggregations or joins.
- Aggregations of data with a high degree of cardinality for the key. A high degree of cardinality means the key contains many different values. A unique key has the highest possible degree of cardinality because every key has a different value. Indexed views improve performance by reducing the number of rows a query has to access. If the view result set has almost as many rows as the base table, then there is little performance benefit from using the view. For example, consider this query on a table that has 1,000 rows:
SELECT PriKey, SUM(SalesCol) FROM ExampleTable GROUP BY PriKeyIf the cardinality of the table key is 100, then an indexed view built using the result of this query would only have 100 rows. Queries using the view would on average need one tenth of the reads needed against the base table. If the key is a unique key, the cardinality of the key is 1000 and the view result set returns 1000 rows. A query has no performance gain from using this indexed view instead of directly reading the base table.
- Expanding joins, which are views whose result sets are larger than the original data in the base tables.
Combining Indexed Views with Queries
Although the restrictions on the types of views that can be indexed may prevent you from designing a view that solves a complete problem, you may be able to design multiple smaller indexed views that speed parts of the process.
Consider these examples:
- A frequently executed query aggregates data in one database, aggregates data in another database, and then joins the results. Because an indexed view cannot reference tables from more than one database, you cannot design a single view to perform the entire process. You can, however, create an indexed view in each database that does the aggregation for that database. If the optimizer can match the indexed views against existing queries, at least the aggregation processing will be speeded up without the need to recode existing queries. Although the join processing is not faster, the overall query is faster because it uses the aggregations stored in the indexed views.
- A frequently executed query aggregates data from several tables, and then uses UNION to combine the results. UNION is not allowed in an indexed view. You can once again design views to do each of the individual aggregation operations. The optimizer can then select the indexed views to speed up queries with no need to recode the queries. While the UNION processing is not improved, the individual aggregation processes are.
Design indexed views that can satisfy multiple operations. Because the optimizer can use an indexed view even when it is not specified in the FROM clause, a well-designed indexed view can speed the processing of many queries.
For example, consider creating an index on this view:
CREATE VIEW ExampleView (PriKey, SumColx, CountColx)
AS
SELECT PriKey, SUM(Colx), COUNT_BIG(Colx)
FROM MyTable
GROUP BY PriKey
Not only can this view satisfy queries that directly reference the view columns, it can also be used to satisfy queries that query the base table and contain expressions such as SUM(Colx), COUNT_BIG(Colx), COUNT(Colx), and AVG(Colx). All such queries will be faster because they only have to retrieve the small number of rows in the view rather than reading the full number of rows from the base tables.