Re: indexed views now exist in Microsoft SQL Server 2000

I can't provide complete details, but I can answer your questions.

1. An indexed view has a complete materialization of all of the columns in the view. Therefore an lookup of a row in the view does a single seek into the index, not multiple seeks into the base table.
2. Creating an index on a computed column is a simple extention to the normal CREATE INDEX semantics: you can specify the name of the computed column in the column list for the index. They are not stored in the base table.

<steve.tolkin_at_fmr.com> wrote in message news:u66rwrqov.fsf_at_fmr.com...
> 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.
Received on Wed May 31 2000 - 00:00:00 CEST