Indexed Views

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 ?Where in BOL?
You can create indexed views with any edition, but they will not be
considered automatically by the query optimizer unless you're using
Enterprise or Developer edition.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> 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 ?
>
>|||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 ?
>
>
>|||This quote is straight from BOL:
"Note You can create indexed views only if you install
Microsoft SQL Server 2000 Enterprise Edition or Microsoft
SQL Server 2000 Developer Edition."
>--Original Message--
>Where in BOL?
>You can create indexed views with any edition, but they
will not be
>considered automatically by the query optimizer unless
you're using
>Enterprise or Developer edition.
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> 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 ?
>>
>>
>
>.
>|||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 ?
> >
> >
> >
> >
> >|||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 ?
> > >
> > >
> > >
> > >
> > >|||Then that's a doc bug. I just ran the following successfully in Northwind
on SQL Server Personal Edition:
create view v
with schemabinding
as
select
CustomerID
, CompanyName
from
dbo.Customers
go
create unique clustered index idx on v (CustomerID)
go
drop view v
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
This quote is straight from BOL:
"Note You can create indexed views only if you install
Microsoft SQL Server 2000 Enterprise Edition or Microsoft
SQL Server 2000 Developer Edition."
>--Original Message--
>Where in BOL?
>You can create indexed views with any edition, but they
will not be
>considered automatically by the query optimizer unless
you're using
>Enterprise or Developer edition.
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> 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 ?
>>
>>
>
>.
>|||Did you not see any of my posts?
"Tom Moreau" wrote:
> Then that's a doc bug. I just ran the following successfully in Northwind
> on SQL Server Personal Edition:
> create view v
> with schemabinding
> as
> select
> CustomerID
> , CompanyName
> from
> dbo.Customers
> go
> create unique clustered index idx on v (CustomerID)
> go
> drop view v
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Ron" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
> This quote is straight from BOL:
>
> "Note You can create indexed views only if you install
> Microsoft SQL Server 2000 Enterprise Edition or Microsoft
> SQL Server 2000 Developer Edition."
>
>
> >--Original Message--
> >Where in BOL?
> >
> >You can create indexed views with any edition, but they
> will not be
> >considered automatically by the query optimizer unless
> you're using
> >Enterprise or Developer edition.
> >
> >--
> >Adam Machanic
> >SQL Server MVP
> >http://www.sqljunkies.com/weblog/amachanic
> >--
> >
> >
> >"Ron" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> >> 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 ?
> >>
> >>
> >>
> >>
> >
> >
> >.
> >
>|||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 ?
> > >
> > >
> > >
> > >
> > >|||I did. What's your point? Ron cited the BOL and the citation is simply
incorrect. The fact remains that you can create an indexed view in a
version other than EE or Dev. It will be materialized - run sp_spaceused to
prove it to yourself. What won't happen with the non EE or Dev versions is
implicit use of the view, nor will it use the explicit indexed view's data
without the NOEXPAND hint.
--
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:B5B7BBFC-408A-4864-962A-B9D77DC2A4FA@.microsoft.com...
Did you not see any of my posts?
"Tom Moreau" wrote:
> Then that's a doc bug. I just ran the following successfully in Northwind
> on SQL Server Personal Edition:
> create view v
> with schemabinding
> as
> select
> CustomerID
> , CompanyName
> from
> dbo.Customers
> go
> create unique clustered index idx on v (CustomerID)
> go
> drop view v
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Ron" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
> This quote is straight from BOL:
>
> "Note You can create indexed views only if you install
> Microsoft SQL Server 2000 Enterprise Edition or Microsoft
> SQL Server 2000 Developer Edition."
>
>
> >--Original Message--
> >Where in BOL?
> >
> >You can create indexed views with any edition, but they
> will not be
> >considered automatically by the query optimizer unless
> you're using
> >Enterprise or Developer edition.
> >
> >--
> >Adam Machanic
> >SQL Server MVP
> >http://www.sqljunkies.com/weblog/amachanic
> >--
> >
> >
> >"Ron" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> >> 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 ?
> >>
> >>
> >>
> >>
> >
> >
> >.
> >
>|||We had customers consider what version to purchased based on the
understanding of how indexed views were not used in anything but Enterprise
Edition.
But, as you indicated - and I also found at this MSDN site
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
Note Indexed views can be created in any edition of SQL Server 2000. In
SQL Server 2000 Enterprise Edition, the indexed view will be automatically
considered by the query optimizer. To use an indexed view in all other
editions, the NOEXPAND hint must be used.
"Tom Moreau" wrote:
> 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 ?
> > > >
> > > >
> > > >
> > > >
> > > >
>|||This KB article corrects the error in BOL.
http://support.microsoft.com/default.aspx?scid=kb;en-us;270054
Apparently that misinformation made it into alot of other publications and
considerations of which edition was appropriate for use. That's a shame.
"Tom Moreau" wrote:
> I did. What's your point? Ron cited the BOL and the citation is simply
> incorrect. The fact remains that you can create an indexed view in a
> version other than EE or Dev. It will be materialized - run sp_spaceused to
> prove it to yourself. What won't happen with the non EE or Dev versions is
> implicit use of the view, nor will it use the explicit indexed view's data
> without the NOEXPAND hint.
> --
> 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:B5B7BBFC-408A-4864-962A-B9D77DC2A4FA@.microsoft.com...
> Did you not see any of my posts?
> "Tom Moreau" wrote:
> > Then that's a doc bug. I just ran the following successfully in Northwind
> > on SQL Server Personal Edition:
> >
> > create view v
> > with schemabinding
> > as
> > select
> > CustomerID
> > , CompanyName
> > from
> > dbo.Customers
> > go
> >
> > create unique clustered index idx on v (CustomerID)
> > go
> >
> > drop view v
> >
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Ron" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
> > This quote is straight from BOL:
> >
> >
> > "Note You can create indexed views only if you install
> > Microsoft SQL Server 2000 Enterprise Edition or Microsoft
> > SQL Server 2000 Developer Edition."
> >
> >
> >
> >
> > >--Original Message--
> > >Where in BOL?
> > >
> > >You can create indexed views with any edition, but they
> > will not be
> > >considered automatically by the query optimizer unless
> > you're using
> > >Enterprise or Developer edition.
> > >
> > >--
> > >Adam Machanic
> > >SQL Server MVP
> > >http://www.sqljunkies.com/weblog/amachanic
> > >--
> > >
> > >
> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> > >> 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 ?
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >.
> > >
> >
> >
>|||Yep, that's the right answer. The poor man's workaround is the NOEXPAND
hint. I prefer EE so that I can avoid that, not to mention the implicit use
of the indexed view - that part is worth its weight in gold. :-)
--
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:7FB508C9-11AC-4496-9328-3C5CC3A07C78@.microsoft.com...
We had customers consider what version to purchased based on the
understanding of how indexed views were not used in anything but Enterprise
Edition.
But, as you indicated - and I also found at this MSDN site
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
Note Indexed views can be created in any edition of SQL Server 2000. In
SQL Server 2000 Enterprise Edition, the indexed view will be automatically
considered by the query optimizer. To use an indexed view in all other
editions, the NOEXPAND hint must be used.
"Tom Moreau" wrote:
> 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 Indexe
d
> 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 ?
> > > >
> > > >
> > > >
> > > >
> > > >
>|||Thanks......This was the answer I was looking for.....
>--Original Message--
>This KB article corrects the error in BOL.
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;270054
>Apparently that misinformation made it into alot of other
publications and
>considerations of which edition was appropriate for use.
That's a shame.
>"Tom Moreau" wrote:
>> I did. What's your point? Ron cited the BOL and the
citation is simply
>> incorrect. The fact remains that you can create an
indexed view in a
>> version other than EE or Dev. It will be materialized -
run sp_spaceused to
>> prove it to yourself. What won't happen with the non
EE or Dev versions is
>> implicit use of the view, nor will it use the explicit
indexed view's data
>> without the NOEXPAND hint.
>> --
>> 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:B5B7BBFC-408A-4864-962A-
B9D77DC2A4FA@.microsoft.com...
>> Did you not see any of my posts?
>> "Tom Moreau" wrote:
>> > Then that's a doc bug. I just ran the following
successfully in Northwind
>> > on SQL Server Personal Edition:
>> >
>> > create view v
>> > with schemabinding
>> > as
>> > select
>> > CustomerID
>> > , CompanyName
>> > from
>> > dbo.Customers
>> > go
>> >
>> > create unique clustered index idx on v (CustomerID)
>> > go
>> >
>> > drop view v
>> >
>> >
>> > --
>> > Tom
>> >
>> > ---
--
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Columnist, SQL Server Professional
>> > Toronto, ON Canada
>> > www.pinnaclepublishing.com
>> >
>> >
>> > "Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
>> > This quote is straight from BOL:
>> >
>> >
>> > "Note You can create indexed views only if you
install
>> > Microsoft SQL Server 2000 Enterprise Edition or
Microsoft
>> > SQL Server 2000 Developer Edition."
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >Where in BOL?
>> > >
>> > >You can create indexed views with any edition, but
they
>> > will not be
>> > >considered automatically by the query optimizer
unless
>> > you're using
>> > >Enterprise or Developer edition.
>> > >
>> > >--
>> > >Adam Machanic
>> > >SQL Server MVP
>> > >http://www.sqljunkies.com/weblog/amachanic
>> > >--
>> > >
>> > >
>> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
>> > message
>> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> > >> 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 ?
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> > >.
>> > >
>> >
>> >
>>
>.
>|||I got this from my BOL:
"Indexed views can be created in any edition of SQL Server 2000. In SQL
Server 2000 Enterprise Edition, the query optimizer will automatically
consider the indexed view. To use an indexed view in all other editions, the
NOEXPAND hint must be used."
My version of the BOL is SP3a. I'm wondering if the misinformation arose
from an earlier cut.
--
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:658130A7-D8D7-44B2-B879-874557E0DF8E@.microsoft.com...
This KB article corrects the error in BOL.
http://support.microsoft.com/default.aspx?scid=kb;en-us;270054
Apparently that misinformation made it into alot of other publications and
considerations of which edition was appropriate for use. That's a shame.
"Tom Moreau" wrote:
> I did. What's your point? Ron cited the BOL and the citation is simply
> incorrect. The fact remains that you can create an indexed view in a
> version other than EE or Dev. It will be materialized - run sp_spaceused
to
> prove it to yourself. What won't happen with the non EE or Dev versions
is
> implicit use of the view, nor will it use the explicit indexed view's data
> without the NOEXPAND hint.
> --
> 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:B5B7BBFC-408A-4864-962A-B9D77DC2A4FA@.microsoft.com...
> Did you not see any of my posts?
> "Tom Moreau" wrote:
> > Then that's a doc bug. I just ran the following successfully in
Northwind
> > on SQL Server Personal Edition:
> >
> > create view v
> > with schemabinding
> > as
> > select
> > CustomerID
> > , CompanyName
> > from
> > dbo.Customers
> > go
> >
> > create unique clustered index idx on v (CustomerID)
> > go
> >
> > drop view v
> >
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > "Ron" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
> > This quote is straight from BOL:
> >
> >
> > "Note You can create indexed views only if you install
> > Microsoft SQL Server 2000 Enterprise Edition or Microsoft
> > SQL Server 2000 Developer Edition."
> >
> >
> >
> >
> > >--Original Message--
> > >Where in BOL?
> > >
> > >You can create indexed views with any edition, but they
> > will not be
> > >considered automatically by the query optimizer unless
> > you're using
> > >Enterprise or Developer edition.
> > >
> > >--
> > >Adam Machanic
> > >SQL Server MVP
> > >http://www.sqljunkies.com/weblog/amachanic
> > >--
> > >
> > >
> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> > >> 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 ?
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >.
> > >
> >
> >
>|||Yes......I had SP2.......That is what I wanted to know
(Whether the functionality was there but the BOL info was
wrong or the functionality was added with the SP's)
>--Original Message--
>I got this from my BOL:
>"Indexed views can be created in any edition of SQL
Server 2000. In SQL
>Server 2000 Enterprise Edition, the query optimizer will
automatically
>consider the indexed view. To use an indexed view in all
other editions, the
>NOEXPAND hint must be used."
>My version of the BOL is SP3a. I'm wondering if the
misinformation arose
>from an earlier cut.
>--
>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:658130A7-D8D7-44B2-B879-874557E0DF8E@.microsoft.com...
>This KB article corrects the error in BOL.
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;270054
>Apparently that misinformation made it into alot of other
publications and
>considerations of which edition was appropriate for use.
That's a shame.
>"Tom Moreau" wrote:
>> I did. What's your point? Ron cited the BOL and the
citation is simply
>> incorrect. The fact remains that you can create an
indexed view in a
>> version other than EE or Dev. It will be materialized -
run sp_spaceused
>to
>> prove it to yourself. What won't happen with the non
EE or Dev versions
>is
>> implicit use of the view, nor will it use the explicit
indexed view's data
>> without the NOEXPAND hint.
>> --
>> 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:B5B7BBFC-408A-4864-962A-
B9D77DC2A4FA@.microsoft.com...
>> Did you not see any of my posts?
>> "Tom Moreau" wrote:
>> > Then that's a doc bug. I just ran the following
successfully in
>Northwind
>> > on SQL Server Personal Edition:
>> >
>> > create view v
>> > with schemabinding
>> > as
>> > select
>> > CustomerID
>> > , CompanyName
>> > from
>> > dbo.Customers
>> > go
>> >
>> > create unique clustered index idx on v (CustomerID)
>> > go
>> >
>> > drop view v
>> >
>> >
>> > --
>> > Tom
>> >
>> > ---
--
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Columnist, SQL Server Professional
>> > Toronto, ON Canada
>> > www.pinnaclepublishing.com
>> >
>> >
>> > "Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
>> > This quote is straight from BOL:
>> >
>> >
>> > "Note You can create indexed views only if you
install
>> > Microsoft SQL Server 2000 Enterprise Edition or
Microsoft
>> > SQL Server 2000 Developer Edition."
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >Where in BOL?
>> > >
>> > >You can create indexed views with any edition, but
they
>> > will not be
>> > >considered automatically by the query optimizer
unless
>> > you're using
>> > >Enterprise or Developer edition.
>> > >
>> > >--
>> > >Adam Machanic
>> > >SQL Server MVP
>> > >http://www.sqljunkies.com/weblog/amachanic
>> > >--
>> > >
>> > >
>> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
>> > message
>> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> > >> 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 ?
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> > >.
>> > >
>> >
>> >
>>
>.
>|||Now you have another reason to install SP3a... ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b5f01c4eead$9a8e5ab0$a601280a@.phx.gbl...
Yes......I had SP2.......That is what I wanted to know
(Whether the functionality was there but the BOL info was
wrong or the functionality was added with the SP's)
>--Original Message--
>I got this from my BOL:
>"Indexed views can be created in any edition of SQL
Server 2000. In SQL
>Server 2000 Enterprise Edition, the query optimizer will
automatically
>consider the indexed view. To use an indexed view in all
other editions, the
>NOEXPAND hint must be used."
>My version of the BOL is SP3a. I'm wondering if the
misinformation arose
>from an earlier cut.
>--
>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:658130A7-D8D7-44B2-B879-874557E0DF8E@.microsoft.com...
>This KB article corrects the error in BOL.
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;270054
>Apparently that misinformation made it into alot of other
publications and
>considerations of which edition was appropriate for use.
That's a shame.
>"Tom Moreau" wrote:
>> I did. What's your point? Ron cited the BOL and the
citation is simply
>> incorrect. The fact remains that you can create an
indexed view in a
>> version other than EE or Dev. It will be materialized -
run sp_spaceused
>to
>> prove it to yourself. What won't happen with the non
EE or Dev versions
>is
>> implicit use of the view, nor will it use the explicit
indexed view's data
>> without the NOEXPAND hint.
>> --
>> 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:B5B7BBFC-408A-4864-962A-
B9D77DC2A4FA@.microsoft.com...
>> Did you not see any of my posts?
>> "Tom Moreau" wrote:
>> > Then that's a doc bug. I just ran the following
successfully in
>Northwind
>> > on SQL Server Personal Edition:
>> >
>> > create view v
>> > with schemabinding
>> > as
>> > select
>> > CustomerID
>> > , CompanyName
>> > from
>> > dbo.Customers
>> > go
>> >
>> > create unique clustered index idx on v (CustomerID)
>> > go
>> >
>> > drop view v
>> >
>> >
>> > --
>> > Tom
>> >
>> > ---
--
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Columnist, SQL Server Professional
>> > Toronto, ON Canada
>> > www.pinnaclepublishing.com
>> >
>> >
>> > "Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
>> > This quote is straight from BOL:
>> >
>> >
>> > "Note You can create indexed views only if you
install
>> > Microsoft SQL Server 2000 Enterprise Edition or
Microsoft
>> > SQL Server 2000 Developer Edition."
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >Where in BOL?
>> > >
>> > >You can create indexed views with any edition, but
they
>> > will not be
>> > >considered automatically by the query optimizer
unless
>> > you're using
>> > >Enterprise or Developer edition.
>> > >
>> > >--
>> > >Adam Machanic
>> > >SQL Server MVP
>> > >http://www.sqljunkies.com/weblog/amachanic
>> > >--
>> > >
>> > >
>> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
>> > message
>> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> > >> 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 ?
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> > >.
>> > >
>> >
>> >
>>
>.
>|||There's an updated BOL beyond SP3a:
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u%23NTE6q7EHA.3836@.tk2msftngp13.phx.gbl...
> Now you have another reason to install SP3a... ;-)
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Ron" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b5f01c4eead$9a8e5ab0$a601280a@.phx.gbl...
> Yes......I had SP2.......That is what I wanted to know
> (Whether the functionality was there but the BOL info was
> wrong or the functionality was added with the SP's)
> >--Original Message--
> >I got this from my BOL:
> >
> >"Indexed views can be created in any edition of SQL
> Server 2000. In SQL
> >Server 2000 Enterprise Edition, the query optimizer will
> automatically
> >consider the indexed view. To use an indexed view in all
> other editions, the
> >NOEXPAND hint must be used."
> >
> >My version of the BOL is SP3a. I'm wondering if the
> misinformation arose
> >from an earlier cut.
> >
> >--
> >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:658130A7-D8D7-44B2-B879-874557E0DF8E@.microsoft.com...
> >This KB article corrects the error in BOL.
> >
> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;270054
> >
> >Apparently that misinformation made it into alot of other
> publications and
> >considerations of which edition was appropriate for use.
> That's a shame.
> >
> >"Tom Moreau" wrote:
> >
> >> I did. What's your point? Ron cited the BOL and the
> citation is simply
> >> incorrect. The fact remains that you can create an
> indexed view in a
> >> version other than EE or Dev. It will be materialized -
> run sp_spaceused
> >to
> >> prove it to yourself. What won't happen with the non
> EE or Dev versions
> >is
> >> implicit use of the view, nor will it use the explicit
> indexed view's data
> >> without the NOEXPAND hint.
> >>
> >> --
> >> 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:B5B7BBFC-408A-4864-962A-
> B9D77DC2A4FA@.microsoft.com...
> >> Did you not see any of my posts?
> >>
> >> "Tom Moreau" wrote:
> >>
> >> > Then that's a doc bug. I just ran the following
> successfully in
> >Northwind
> >> > on SQL Server Personal Edition:
> >> >
> >> > create view v
> >> > with schemabinding
> >> > as
> >> > select
> >> > CustomerID
> >> > , CompanyName
> >> > from
> >> > dbo.Customers
> >> > go
> >> >
> >> > create unique clustered index idx on v (CustomerID)
> >> > go
> >> >
> >> > drop view v
> >> >
> >> >
> >> > --
> >> > Tom
> >> >
> >> > ---
> --
> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > SQL Server MVP
> >> > Columnist, SQL Server Professional
> >> > Toronto, ON Canada
> >> > www.pinnaclepublishing.com
> >> >
> >> >
> >> > "Ron" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
> >> > This quote is straight from BOL:
> >> >
> >> >
> >> > "Note You can create indexed views only if you
> install
> >> > Microsoft SQL Server 2000 Enterprise Edition or
> Microsoft
> >> > SQL Server 2000 Developer Edition."
> >> >
> >> >
> >> >
> >> >
> >> > >--Original Message--
> >> > >Where in BOL?
> >> > >
> >> > >You can create indexed views with any edition, but
> they
> >> > will not be
> >> > >considered automatically by the query optimizer
> unless
> >> > you're using
> >> > >Enterprise or Developer edition.
> >> > >
> >> > >--
> >> > >Adam Machanic
> >> > >SQL Server MVP
> >> > >http://www.sqljunkies.com/weblog/amachanic
> >> > >--
> >> > >
> >> > >
> >> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
> >> > message
> >> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
> >> > >> 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 ?
> >> > >>
> >> > >>
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> > >.
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >.
> >
>|||>> Yes......I had SP2.......That is what I wanted to know
>> (Whether the functionality was there but the BOL info was
>> wrong or the functionality was added with the SP's)
The BOL information was wrong in the RTM version.
>> Now you have another reason to install SP3a... ;-)
Just to clarify - Service Packs never include an updated version of BOL.
The size of BOL makes that impractical to include in the Service Pack. But,
as Adam pointed out, we have updated the BOL available for download at this
link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
>
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ubTyiYr7EHA.2608@.TK2MSFTNGP10.phx.gbl...
> There's an updated BOL beyond SP3a:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:u%23NTE6q7EHA.3836@.tk2msftngp13.phx.gbl...
>> Now you have another reason to install SP3a... ;-)
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Ron" <anonymous@.discussions.microsoft.com> wrote in message
>> news:0b5f01c4eead$9a8e5ab0$a601280a@.phx.gbl...
>> Yes......I had SP2.......That is what I wanted to know
>> (Whether the functionality was there but the BOL info was
>> wrong or the functionality was added with the SP's)
>> >--Original Message--
>> >I got this from my BOL:
>> >
>> >"Indexed views can be created in any edition of SQL
>> Server 2000. In SQL
>> >Server 2000 Enterprise Edition, the query optimizer will
>> automatically
>> >consider the indexed view. To use an indexed view in all
>> other editions, the
>> >NOEXPAND hint must be used."
>> >
>> >My version of the BOL is SP3a. I'm wondering if the
>> misinformation arose
>> >from an earlier cut.
>> >
>> >--
>> >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:658130A7-D8D7-44B2-B879-874557E0DF8E@.microsoft.com...
>> >This KB article corrects the error in BOL.
>> >
>> >http://support.microsoft.com/default.aspx?scid=kb;en-
>> us;270054
>> >
>> >Apparently that misinformation made it into alot of other
>> publications and
>> >considerations of which edition was appropriate for use.
>> That's a shame.
>> >
>> >"Tom Moreau" wrote:
>> >
>> >> I did. What's your point? Ron cited the BOL and the
>> citation is simply
>> >> incorrect. The fact remains that you can create an
>> indexed view in a
>> >> version other than EE or Dev. It will be materialized -
>> run sp_spaceused
>> >to
>> >> prove it to yourself. What won't happen with the non
>> EE or Dev versions
>> >is
>> >> implicit use of the view, nor will it use the explicit
>> indexed view's data
>> >> without the NOEXPAND hint.
>> >>
>> >> --
>> >> 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:B5B7BBFC-408A-4864-962A-
>> B9D77DC2A4FA@.microsoft.com...
>> >> Did you not see any of my posts?
>> >>
>> >> "Tom Moreau" wrote:
>> >>
>> >> > Then that's a doc bug. I just ran the following
>> successfully in
>> >Northwind
>> >> > on SQL Server Personal Edition:
>> >> >
>> >> > create view v
>> >> > with schemabinding
>> >> > as
>> >> > select
>> >> > CustomerID
>> >> > , CompanyName
>> >> > from
>> >> > dbo.Customers
>> >> > go
>> >> >
>> >> > create unique clustered index idx on v (CustomerID)
>> >> > go
>> >> >
>> >> > drop view v
>> >> >
>> >> >
>> >> > --
>> >> > Tom
>> >> >
>> >> > ---
>> --
>> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> >> > SQL Server MVP
>> >> > Columnist, SQL Server Professional
>> >> > Toronto, ON Canada
>> >> > www.pinnaclepublishing.com
>> >> >
>> >> >
>> >> > "Ron" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >> > news:0b0401c4eea2$7bc16100$a601280a@.phx.gbl...
>> >> > This quote is straight from BOL:
>> >> >
>> >> >
>> >> > "Note You can create indexed views only if you
>> install
>> >> > Microsoft SQL Server 2000 Enterprise Edition or
>> Microsoft
>> >> > SQL Server 2000 Developer Edition."
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > >--Original Message--
>> >> > >Where in BOL?
>> >> > >
>> >> > >You can create indexed views with any edition, but
>> they
>> >> > will not be
>> >> > >considered automatically by the query optimizer
>> unless
>> >> > you're using
>> >> > >Enterprise or Developer edition.
>> >> > >
>> >> > >--
>> >> > >Adam Machanic
>> >> > >SQL Server MVP
>> >> > >http://www.sqljunkies.com/weblog/amachanic
>> >> > >--
>> >> > >
>> >> > >
>> >> > >"Ron" <anonymous@.discussions.microsoft.com> wrote in
>> >> > message
>> >> > >news:08d501c4ee9f$94a57a60$a401280a@.phx.gbl...
>> >> > >> 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 ?
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >.
>> >
>