indexed views now exist in Microsoft SQL Server 2000

Microsoft SQL Server 2000 is still in beta. However it includes two features that are relevant to the discussion held in this newsgroup a few months ago, under the subject "Indexes on Views".

The new features are:

1. indexes on computed columns.
2. indexed views. Unfortunately there are many limitations on the SQL in the view query, e.g. no union, limited aggregates, no outer join, etc. etc.

Some small amount of information is available in SQL2K_Final_Eval.doc at http://www.microsoft.com/sql/productinfo/sql2krev.htm There is also an article by Kalen Delaney in SQL Magazine May 2000 (hard copy or subscribers only).

I would like complete details about how these work.

Does the entire view (every column) get materialized? An alternate strategy would be to just materialize the index, and have the index stores the tuple IDs in order to access the rest of the row.

I am even less clear about how indexes on computed columns work. Do these columns exist in ordinary stored tables only, and/or in views? What is the syntax (DDL) for defining them?

I posted 04/24/2000 to comp.databases.theory something like this:

> 1. As others have said, a query on a view can often benefit from using
> indexes defined on its underlying physical base tables. This
> assumes the optimizer/runtime is good enough to find/use the
> appropriate index(es), which often is not the case.
>
> 2. A materialized view is a physically stored table that corresponds
> to (has the same content as) a select query. These too can
> generally have indexes defined that will be used (hopefully).
>
> 3. My main point is that indexes on ordinary views are perfectly well
> defined, useful, and have been implemented in database systems
> supporting the relational model. (It is a pity that they are not
> supported by the SQL standard. This is just another case where SQL
> does not cleanly separate the logical relational model from its
> physical implementation.)
>
> This technique (indexing a view) is useful for almost any computed
> column. One specific example would be to support case insensitive
> searching, e.g. of names. The view could have a column
> e.g. upper(original_last_name) as last_name, with an index defined
> on it. Then a typical query would be: select original_last_name
> from people where last_name like 'SMITH%'
>
> The point of using an index on the view column is to save space; this
> avoids storing the uppercase version of the name column both in the
> table (where it is not needed) and in the index.
 

Hopefully helpfully yours,
Steve

-- 
Steven Tolkin          steve.tolkin_at_fmr.com      617-563-0516 
Fidelity Investments   82 Devonshire St. R24D    Boston MA 02109
There is nothing so practical as a good theory.  Comments are by me, 
not Fidelity Investments, its subsidiaries or affiliates.