Re: indexed views now exist in Microsoft SQL Server 2000

Internally, all indexes in SQL Server are similar to tables; the only exception are heaps. I think the confusion of terminology existed before Microsoft entered the fray: runtime view materialization is a query optimization/execution technique that I first heard about in DB2/MVS in the early 90s. Oracle describes their summary tables and join indexes they introduces Oracle 7 as "materialized views." I believe Microsoft chose their terminology to differentiate it from Oracle's and also because it is more accurate in terms of how the functionality is exposed to the user: you create a view then you create an index on the view.

Runtime view materialization of "normal views" is not done directly by SQL Server because the view columns, tables, and predicates are "merged" into the referencing query before optimization; the entire expanded query is then optimized. This is a more general solution that "view materialization" and I believe both Oracle and DB2 Common Server do similar things.

The indexed view is known to the query optimizer to any updates to base tables will have appropriate modifications made in the indexed view as part of the same transaction. There is no facility for deferred updates of these structures, since they would not reflect the current data. Microsoft's OLAP Server fills that need.

In summary, "indexed views" are a physical design choice available to the database developer. "Materialized views" was already an overloaded term.

Ruud de Koter <ruud_dekoter_at_hp.com> wrote in message news:3937767D.2BBD0891_at_hp.com...
> Hi,
>
> Lewis Bruck wrote:
> >
> > 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.
> >
>
> Is it too rash to conclude that an indexed view is therefore no longer
> a view but a table? In other words: is MS once again trying to change
> the meaning of a generally accepted term into the opposite?
>
> Also a point of interest: how will SQL Server keep the view's contents
> synchronized with the underlying table's contents? Will this mechanism
> be as inherently safe as runtime composition of the view?
>
> (left out the rest of the original message)
>
> Love to hear from others what they think of this...
>
> Regards,
>
> Ruud
> --
> --------------------------------------------------------------------------