Indexed Views in Enterprise Edition...?

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!!"JM" <JM@.nospam.com> wrote in message
news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> 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/pr.../ipsql05iv.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%230XpE0jEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> "JM" <JM@.nospam.com> wrote in message
> news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> The BOL has a pretty decent description. Search for the following:
> "Designing an Indexed View"
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||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.
Hope this helps.
Dan Guzman
SQL Server MVP
"JM" <JM@.nospam.com> wrote in message
news:%23F5n2tjEGHA.140@.TK2MSFTNGP12.phx.gbl...
> 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!!
>