We are using SQL Server 2000 Standard Edition.  Among other things, the
"Enterprise" edition adds "indexed views".

Could someone tell me what indexed views are, and what they are good for?
Is this simply an index on a view?  Do the underlying tables have to be
static for the index to be effective?  Pros/cons?

Thanks!!

The BOL has a pretty decent description.   Search for the following:
"Designing an Indexed View"



Rick Sawtell
MCT, MCSD, MCDBA

Also, take a look at this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
Show quoteHide quote

Indexed views are also available in other SQL Server editions.  However, in
Enterprise Edition, indexes on views are automatically considered by the
optimizer and even when the view is not referenced.  A NOEXPAND hint is
needed to use view indexes in other editions.

The Books Online describes indexed views in much more detail than can be
discussed here but a short answer is that view indexes contain data
materialized from the underlying tables.  This redundant data is
automatically maintained by SQL Server as the underlying data changes.
Consequently, data is dynamic rather than static.

Indexed views are especially nice for aggregated data and can also be used
to avoid complex joins.  This can significantly reduce the work needed to
retrieve data by reporting applications with large data volumes.  The
downsides are that there are many restrictions on using indexed views (see
BOL) and additional overhead is needed to maintain the view index(es).  In
my experience, indexed views may be appropriate for reporting databases but
need to be used carefully in OLTP apps.

Show quoteHide quote

Joing two tables but avoid cartesian product
Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?
multiple inserts in transaction
Add a field