What happens when I create an index on a view? Are there any benefits versus using a table with indexes?

Question

From what I understand the data will be "persisted" (physically stored, not just retrieved when i need it). Is this correct?

If so what would be the advantage of using an indexed view versus just using a table?

Answer 1

Indexed views can increase query performance in the following ways:

• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

Improving Performance with SQL Server 2005 Indexed Views

Answer 2

We use Indexed views to "pre JOIN" several commonly used tables that are used in many places.

They are also useful to implement filtered indexes before SQL Server 2008

However, MSDN has an article on them: "Improving Performance with SQL Server 2005 Indexed Views" (first hit on Google BTW)

Answer 3

Let's look at things from a really high level first. A relation is a mapping between tuples and logical values (usually true, false and null but you could theoretically have many more).

A father/son relation maps a tuple of the form (person, person) to the set {true, false, null}.

So in an ideal world you could ask an all knowing entity (i.e. an oracle) about any father/son pair and get a yes/no/ answer.

Now, a computer obviously can't search the entire space of all possible father and son pairs. You have to tell where to start searching and how far to search before it gives up. This is what a "table" is. We traditionally think of tables as sets but really we should be thinking of them more explicitly as bounds on a search space.

An index, as you know, is just a way of organizing the data so that it can search the table faster (make fewer comparisons). So an index is a way of further contracting your search space (but on a case by case basis).

Now, when you write a query that joins tables T_1, T_2, T_3..., T_n the query optimizer tries to arrange them so that it considers the tables with the smallest search space first. Furthermore, the final result set is going to be indexable (i.e. the search space is going to be contractable) in a way that the constituent tables are not (for instance you might be concatenating strings in the select statement)

When you "index" a view all you're doing is telling SQL Server to remember the structure of the index on the view so that in the future it might be able to use it in some other similar ad-hoc query that you create.

For instance, as part of some big complicated query (BCQ) you might be concatenating strings on columns c1 and c2. That is normally a pretty big search space but with an indexed view the query optimizer would realize that it already has an index on the space of objects that are of the form c1 + c2. Furthermore the query optimizer might be able to reduce BCQ to a view or two (or three or ...).

Answer 4

You attempt to compare an indexed view and, say, just another table.

If you want this other table to always be consistent with the other tables in the system (i.e. the tables that the indexed view references), you would have to write a number of triggers for these other tables, in order for that to work. And if the view is reasonable complex (multiple base tables, complex where clause or aggregates), those triggers could be difficult to write and get correct.

But you don't have to do this work, because it's already built into SQL server as indexed views - just think of it as SQL server automatically writing all of those triggers, and getting it all right first time.