Re: Indexed Views

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Apple continues to confuse pundits by doing more than one thing at once
    ... So how did you import the XML file into SQL Server Steve? ... of another author and the representation of them as one's own original ... Never claimed it as my original work. ...
    (comp.sys.mac.advocacy)
  • 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: 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)