Re: Indexed Views

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 14:34:03 -0500

Not entirely true. Create and indexed view and try:

select
 *
from
 v with (noexpand) 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Steve Z" <SteveZ@discussions.microsoft.com> wrote in message
news:186BC65D-F912-4585-8952-1C00F7E8BCD9@microsoft.com...
pg 326 Professional SQL Server2000 Programming - WROX publishers...
"If probably deserves to be reiterated: Indexed views are only supported by
Enterprise, DEveloper and Evaluation editions.  The other editions...allow
you to create an index on a view (to avoid syntax errors when migrating to
one of the other editions), but the query optimzer will not use the Indexed
View in the query plan.
"Steve Z" wrote:
> Pg 443 of Inside MS SQL Server 2000...
>
> The first index you must build on a view is a clustered index, and since
the
> clestered index contains all the data at its leaf level, this index
actually
> does materialize the view.  The view's data is physically stored at the
leaf
> level of the clustered index...
>
> {There are many requirements for being able to index a view...to see if
you
> can index a view}
>
> SELECT ObjectProperty(object_id('SomeViewName'),'IsIndexable')
>
> Returning 1 means you can index - otherwise you have something in the view
> that makes it not indexable (TOP, DISTINCT, subqueries - the list goes
on).
>
> "Steve Z" wrote:
>
> > My understanding is that indexing a view will "materialize" that view in
only
> > the ENTERPRISE edition.  Otherwise, it's "simulated" - so you won't see
the
> > same speed results in a different edition.
> >
> > "Ron" wrote:
> >
> > > According to:
> > > http://msdn.microsoft.com/library/default.asp?
> > > url=/library/en-us/dnsql2k/html/indexedviews1.asp?
> > > frame=true
> > >
> > > "Indexed views can be created in any edition of SQL Server
> > > 2000" (Read the Note on the page) and According to BOL:
> > >
> > > "You can create indexed views only if you install
> > > Microsoft SQL Server 2000 Enterprise Edition or Microsoft
> > > SQL Server 2000 Developer Edition".
> > >
> > > Am I Missing something ?. Is this functionality came with
> > > one of the service packs ?
> > >
> > >
> > >
> > >
> > >

Relevant Pages

  • Re: One Web Service updates SQL, the other cant
    ... Here is the dialog between Tom and I. ... Columnist, SQL Server Professional ... "Steve Ricketts" wrote in message ... I guess its because this is my first Web Service and I> just used the access methods I knew. ...
    (microsoft.public.vb.database.ado)
  • Re: is it possible to re-build index without logging?
    ... Microsoft SQL Server Storage Engine ... "Steve Lin" wrote in message ... >> Andrew J. Kelly SQL MVP ... >>> the log file run out of space. ...
    (microsoft.public.sqlserver.server)
  • Re: us date format
    ... >>> I don't know the context where Steve recommended using CLng, ... I don't disagree with this statement: I believe SQL Server uses a different ... there's no reason why you can't use this "trick" to set the ...
    (microsoft.public.access.formscoding)
  • Re: Trim Email Address down to domain only
    ... SQL Server uses single quotes, ... "Steve Roberts" wrote in message ... >> Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • Re: Indexes
    ... In both queries the optimizer was used clustered ... Only one row fit per page, so SQL Server ... leaf level of the NC index ...
    (microsoft.public.sqlserver.server)