Indexed Views and Performance

Adding indexes to tables is a generally accepted means for improving database performance. Indexes provide a keyed lookup to rows of data that can improve database access and avoid the performance nightmare of a table scan where the entire contents of a table are searched. The same basic principles apply to indexes on views, but indexed views are best utilized to increase performance in the following scenarios:

• Aggregations such as SUM or AVG can be precomputed and stored in the index to minimize the potentially expensive computations during query execution.

• Large table joins can be persisted to eliminate the need to write a join when retrieving the data.

• A combination of aggregations and large table joins ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.