Why Indexed Views are Cool

One of the most important benefits of Indexed Views (aka materialized views) is the ability to materialize summary aggregates of large tables. Normal views are only saved queries and do not store the results. Every time the view is referenced, the aggregation to produce the grouped results must be recomputed.

However when you create an index on the view, the aggregate data is stored in the leaf level of the index. Aggregate and reporting queries can then be processed using the indexed views without having to scan underlying large tables. Yeah, read that again, its pretty damn cool.

The first index you must build on a view is a clustered index, and because the clustered index contains all the data at its leaf level, this index actually does materialize the view. The views data is physically stored at the leaf level of the clustered index.

Because of their special nature, Indexed Views (and Indexed Computed Columns) must only contain deterministic functions (a function that returns the same result every time it is called with the same set of input values). Expressions or functions that return float or real values are not acceptable since these values are imprecise as they can be computed differently on different system architectures.