Indexes and Deadlocks: according

Showing posts with label according. Show all posts

Monday, March 19, 2012

Indexing image - file size limit?

According to the Books Online information on full-text indexing, there
are limits on the size of a file that can be indexed in an image
column: 16MB filesize, 256 KB of filtered text. I've exceeded those
limits in my testing (with Word docs), and still appear to be able to
access information in those files with CONTAINS. Is the documentation
out of date? Are there only certain conditions under which those limits
apply? The word I'm searching for appears only at the end of the test
document, so it's not indexing only the first part of the file...
Since it seems to be a common question here, this is my @.@.version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
And, just for clarity, I don't have any problem with SQL Server
indexing more than I had planned on, I just don't want any surprises
down the road.
Thanks for any ideas you have,
Joel
Last time I tested, when the hard limit was exceeded the remaining content
was not indexed.
So if you index a document containing more than 256k of text, and then put
the word rats at the end, and then tried to search on the word rats, you
would not get hits to this row, if the word rats did not occur in the first
256k of text.
One question for you is did these word docs contains any images? Images will
not be indexed, and can swell the document size, without pushing you over
the 256 k limit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105371120.743181.194610@.c13g2000cwb.googlegr oups.com...
> According to the Books Online information on full-text indexing, there
> are limits on the size of a file that can be indexed in an image
> column: 16MB filesize, 256 KB of filtered text. I've exceeded those
> limits in my testing (with Word docs), and still appear to be able to
> access information in those files with CONTAINS. Is the documentation
> out of date? Are there only certain conditions under which those limits
> apply? The word I'm searching for appears only at the end of the test
> document, so it's not indexing only the first part of the file...
> Since it seems to be a common question here, this is my @.@.version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: )
> And, just for clarity, I don't have any problem with SQL Server
> indexing more than I had planned on, I just don't want any surprises
> down the road.
> Thanks for any ideas you have,
> Joel
>
|||No, the documents that are confusing me did not have any images. They
were just a bunch of text, pasted repeatedly. I ran them through
filtdump, to make sure they really did have more than 256K of text. The
test you describe is exactly what I did--I put words at the very end of
the document that I was sure weren't in the document before, and once
the catalog rebuilt, I searched for them, and found them.
Thanks,
Joel
|||Let me try this myself. I did try this several years ago so this may have
changed with a recent sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||Joel,
Q. Is the documentation out of date?
A. Actually, it is wrong as there is a DOC bug filed for this limited in the
BOL title "Filtering Supported File Types" - "Note For full-text indexing,
a document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text" and this limit can be
over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search May
Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...;en-us;308771. and the
FilterProcessMemoryQuota registry key value. However, you should be careful
in making adjustments to this registry key and incrementally increase it
based upon your server's memory and avg. file sizes.
Q. Are there only certain conditions under which those limits apply?
A. Not specific conditions, but you should ensure that you have enough disk
free space (at least always 15% free) at all times where you have your FT
Catalog folder located as temp. files are written out as needed for the
processing of large files at the same location.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||I'm not entirely sure I'm clear. If I'm reading that article right, it
looks like there is still some point at which indexing a document will
fail due to lack of memory. However, that point cannot be determined by
examining the file size of the document. Is that accurate?
Thanks,
Joel
John Kane wrote:
> Joel,
> Q. Is the documentation out of date?
> A. Actually, it is wrong as there is a DOC bug filed for this limited
in the
> BOL title "Filtering Supported File Types" - "Note For full-text
indexing,
> a document must be less than 16 megabytes (MB) in size and must not
contain
> more than 256 kilobytes (KB) of filtered text" and this limit can be
> over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search
May
> Not Return Any Hits If It Fails to Index a File" at
> http://support.microsoft.com/default...;en-us;308771. and
the
> FilterProcessMemoryQuota registry key value. However, you should be
careful
> in making adjustments to this registry key and incrementally increase
it
> based upon your server's memory and avg. file sizes.
> Q. Are there only certain conditions under which those limits apply?
> A. Not specific conditions, but you should ensure that you have
enough disk
> free space (at least always 15% free) at all times where you have
your FT
> Catalog folder located as temp. files are written out as needed for
the[vbcol=seagreen]
> processing of large files at the same location.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
They[vbcol=seagreen]
The[vbcol=seagreen]
end of[vbcol=seagreen]
once[vbcol=seagreen]
|||You're welcome, Joel,
Yea, the RESOLUTION section states "Unfortunately, there is no way to
calculate directly from the size of the document to be full-text indexed how
much memory the filter process needs. The memory quota only exists to
protect against badly written filters, and they do spike to large amounts if
some bogus size contains a negative number. The quota itself can be made
larger, as long as it is finite. "
While no upper limit size for documents to be FT Indexed is documented, you
can increase the amount of text to be indexed by modifying the
FilterProcessMemoryQuota registry key value and you need to test your
documents on your server to get a feel for what is the "finite" limit and
monitor the server's application event log for "Microsoft Search" source
events for very large files that fail.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105452953.633306.318410@.f14g2000cwb.googlegr oups.com...
> I'm not entirely sure I'm clear. If I'm reading that article right, it
> looks like there is still some point at which indexing a document will
> fail due to lack of memory. However, that point cannot be determined by
> examining the file size of the document. Is that accurate?
> Thanks,
> Joel
> John Kane wrote:
> in the
> indexing,
> contain
> May
> the
> careful
> it
> enough disk
> your FT
> the
> They
> The
> end of
> once
>
|||I just tried it again. I indexed a 32 Mg text and a 16 Mg word doc and have
confirmed that at least first 256 k of extracted text is indexed, but that
tokens at the end of the documents are not. Any textual data after this 256k
boundary appears to be ignored.
I have the same version of SQL Server as you, only I am running on Win2k.
Let me try with Win2003.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6I6P509EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Let me try this myself. I did try this several years ago so this may have
> changed with a recent sp.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
>

Sunday, February 19, 2012

Indexed Views Vs temp tables

With my understanding of indexed views and according to books I read
"indexed views" are supposed to perform much better than "temp tables"
(temp table having primary key and indexed view with clustered index
on the same keys).

But when I tried in my system I am getting opposite results. With
Indexed Views it takes 3 times more time.

Any body has any reasons for that? Or my understanding was wrong?

thanks
Raghu AvirneniIndexed views are expensive when adding data, since adding to the base table
also has to update the view's indexes as well as the base table.

It only helps on the retrieve (and only if the index on the view is used in
the query plan).

I have only used them for lookup tables that rarely change.

"Avirneni" <ravirneni@.trafficmp.com> wrote in message
news:15a48475.0410061606.24dd220d@.posting.google.c om...
> With my understanding of indexed views and according to books I read
> "indexed views" are supposed to perform much better than "temp tables"
> (temp table having primary key and indexed view with clustered index
> on the same keys).
> But when I tried in my system I am getting opposite results. With
> Indexed Views it takes 3 times more time.
> Any body has any reasons for that? Or my understanding was wrong?
>
> thanks
> Raghu Avirneni|||Temp tables and views are different animals. Like David mentioned, examine
the query plan to ensure the index on the view is actually being used.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Indexed Views are they supported?

According the documentation in 2000 and 2005 indexed views are only supporte
d
in the Enterprise edition. So please explain why I can create a unique
clustered index on a view that is schemabound on the standard edition of SQL
Server 2000? Is an index on a view different then and indexed view? If so
please explain. Could it be that creating indexed views is supported in all
versions, but only the using the GUI (EM or Management Studio) to create the
views is not supported in Enterprise and Developer edition? I noticed that
"Manage Indexes...", is grayed out of the "All Tasks" drop down on a view in
EM, and I am running standard edition.You can create the index on any edition, however on the lower editions, it
will not be automatically considered in the query plan unless you use a
specific hint in the query.
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:9A56678F-407D-41B2-BD4B-5A95C259EA97@.microsoft.com...
> According the documentation in 2000 and 2005 indexed views are only
> supported
> in the Enterprise edition. So please explain why I can create a unique
> clustered index on a view that is schemabound on the standard edition of
> SQL
> Server 2000? Is an index on a view different then and indexed view? If
> so
> please explain. Could it be that creating indexed views is supported in
> all
> versions, but only the using the GUI (EM or Management Studio) to create
> the
> views is not supported in Enterprise and Developer edition? I noticed
> that
> "Manage Indexes...", is grayed out of the "All Tasks" drop down on a view
> in
> EM, and I am running standard edition.|||So why is "Managed Indexes" grayed out in EM?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can create the index on any edition, however on the lower editions, it
> will not be automatically considered in the query plan unless you use a
> specific hint in the query.
>
>
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:9A56678F-407D-41B2-BD4B-5A95C259EA97@.microsoft.com...
>
>|||> So why is "Managed Indexes" grayed out in EM?
I have no idea; I use EM for managing jobs and DTS and that's about it.
Stretching here, because I honestly don't believe EM is this smart, but is
it possible that this indexed view has the only index in the database?
A|||On Wed, 19 Apr 2006 09:17:03 -0700, Greg Larsen wrote:

>So why is "Managed Indexes" grayed out in EM?
Hi Greg,
I just ran a quick test on my copy of EM (connected to a developer
edition of SQL Server 2000). If I create a view with schemabinding, I
can access the "Manage indexes" option in EM. If I drop the view and
recreate it without schemabinding, then (after refreshing the list of
views) the "Manage indexes" option is greyed out.
Have yoou tried it on a view that was created with schemabinding and
that further also satisfies all requirements for creating an indexed
view?
Hugo Kornelis, SQL Server MVP

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 ?
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >.
>> >
>

Monday, March 19, 2012

Indexing image - file size limit?

Sunday, February 19, 2012

Indexed Views Vs temp tables

Indexed Views are they supported?

Indexed Views

Indexes and Deadlocks

Blog Archive

About Me