Indexed Views on SQL Server 2005 Non Enterprise

The feature matrix for SQL Server 2005 states:
"Indexed view matching by the query processor is only supported in
Enterprise Edition."
What does this mean in English? We use indexed views *a lot*. What is
"Indexed view matching by query processor" ?!?
Thanks
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.comBecause SQL is on-disk compatible throughout all editions, indexed views
exist in all editions. The query processor will only use them as a means to
resolve a query in Enterprise Edition. In short, they exist but serve no
useful function except in Enterprise Edition.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uktHeu56FHA.1484@.tk2msftngp13.phx.gbl...
> The feature matrix for SQL Server 2005 states:
> "Indexed view matching by the query processor is only supported in
> Enterprise Edition."
> What does this mean in English? We use indexed views *a lot*. What is
> "Indexed view matching by query processor" ?!?
> Thanks
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com|||"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eW2DUX66FHA.2176@.TK2MSFTNGP14.phx.gbl...
> Because SQL is on-disk compatible throughout all editions, indexed views
> exist in all editions. The query processor will only use them as a means
> to resolve a query in Enterprise Edition. In short, they exist but serve
> no useful function except in Enterprise Edition.
> --
Not true. Indexed views are quite usefull in all editions. You must
explicitly query them in other editions, however. To use the indexed view
you need to reference the view directly and use the NOEXPAND hint. In
Enterprise Edition the Query engine will consider rewriting queries against
the base table(s) to go against the indexed view.
"Indexed Views" are in all editions; "Indexed View Query Rewrite" is an EE
feature.
Here's an example:
drop table t
create table t(id int primary key, name varchar(50) null, status int)
insert into t(id,name,status) values (1,'joe',1)
insert into t(id,name,status) values (2,'fred',0)
insert into t(id,name,status) values (2,'alex',0)
go
create view vt
with schemabinding
as
select id,name from dbo.t where status = 1
go
create unique clustered index ix_vt
on vt(id)
go
set showplan_text on
go
select id,name from vt (noexpand)
outputs
StmtText
--
select id,name from vt (noexpand)
(1 row(s) affected)
StmtText
---
|--Clustered Index Scan(OBJECT:([test].[dbo].[vt].[ix_vt]))
(1 row(s) affected)
David|||Geoff N. Hiten wrote:
> Because SQL is on-disk compatible throughout all editions, indexed
> views exist in all editions. The query processor will only use them
> as a means to resolve a query in Enterprise Edition. In short, they
> exist but serve no useful function except in Enterprise Edition.
I apologize for beating a dead horse, but this information is so
shocking that I want to make sure I really understand.
With SQL Server 2005 Standard, there is absolutely zero benefit by
adding an index to a view?
If so, I expect this is going to cause a major performance impact to
our customers. And paying the extra $$$ just for indexed views will
not be an option for them.
I can't believe Microsoft took a feature available in SQL Server 2000
and promoted it to Enterprise only.
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||> "Indexed Views" are in all editions; "Indexed View Query Rewrite" is
> an EE feature.
Thank you for the clarification!
If anyone from Microsoft marketing is listening, the product matrix at
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
should really be clarified.
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||As David pointed out, you can create them and use them, but not
transparently. In EE, if you query a base table and you would have been
better off querying the indexed view, the optimizer uses the indexed view.
In other editions, you have to explicitly use the indexed view rather than
the base table. Sorry if that wasn't clear from my earlier post.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||The feature usage is exactly the same as SQL Server 2000. Direct use of
Indexed Views is supported in all editions. Transparent use of them when
querying a base table is an EE-only feature.
--
Hal Berenson, President
PredictableIT, LLC
www.predictableit.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||Hi Jon
Microsoft did not do that. This is exactly the same behavior as in SQL 2000.
You can only use indexed views in Standard edition of SQL 2000 if you
reference them directly:
SELECT ... FROM my_indexed_view
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:OGzbVq66FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Geoff N. Hiten wrote:
>> Because SQL is on-disk compatible throughout all editions, indexed
>> views exist in all editions. The query processor will only use them
>> as a means to resolve a query in Enterprise Edition. In short, they
>> exist but serve no useful function except in Enterprise Edition.
> I apologize for beating a dead horse, but this information is so
> shocking that I want to make sure I really understand.
> With SQL Server 2005 Standard, there is absolutely zero benefit by
> adding an index to a view?
> If so, I expect this is going to cause a major performance impact to
> our customers. And paying the extra $$$ just for indexed views will
> not be an option for them.
> I can't believe Microsoft took a feature available in SQL Server 2000
> and promoted it to Enterprise only.
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
> Hi Jon
> Microsoft did not do that. This is exactly the same behavior as in SQL
> 2000. You can only use indexed views in Standard edition of SQL 2000 if
> you reference them directly:
> SELECT ... FROM my_indexed_view
>
Should be:
SELECT ... FROM my_indexed_view (NOEXPAND)
BOL:
Indexed views can be created in any edition of SQL Server 2005. In SQL
Server 2005 Enterprise Edition, the query optimizer automatically considers
the indexed view. To use an indexed view in all other editions, the NOEXPAND
table hint must be used.
David|||Kalen Delaney wrote:
> Microsoft did not do that. This is exactly the same behavior as in
> SQL 2000. You can only use indexed views in Standard edition of SQL
> 2000 if you reference them directly:
> SELECT ... FROM my_indexed_view
Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
SQL books ever published. Any chance of a 2005 edition?
--
Jon Robertson
Borland Certified Advanced Delphi 7 Developer
MedEvolve, Inc
http://www.medevolve.com|||And, in SQL Server 2005, you have to use the WITH keyword, which is optional
in 2000
SELECT ... FROM my_indexed_view WITH (NOEXPAND)
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23$J9r%2396FHA.1184@.TK2MSFTNGP12.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23%23Mo$V86FHA.268@.TK2MSFTNGP10.phx.gbl...
>> Hi Jon
>> Microsoft did not do that. This is exactly the same behavior as in SQL
>> 2000. You can only use indexed views in Standard edition of SQL 2000 if
>> you reference them directly:
>> SELECT ... FROM my_indexed_view
> Should be:
> SELECT ... FROM my_indexed_view (NOEXPAND)
> BOL:
> Indexed views can be created in any edition of SQL Server 2005. In SQL
> Server 2005 Enterprise Edition, the query optimizer automatically
> considers the indexed view. To use an indexed view in all other editions,
> the NOEXPAND table hint must be used.
>
> David
>
>|||Thanks Jon ...
I'm working on the next one...
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Jon Robertson" <JonRobertson@.community.nospam> wrote in message
news:uJfXwg$6FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Kalen Delaney wrote:
>> Microsoft did not do that. This is exactly the same behavior as in
>> SQL 2000. You can only use indexed views in Standard edition of SQL
>> 2000 if you reference them directly:
>> SELECT ... FROM my_indexed_view
> Thanks Kalen. By the way, Inside SQL Server 2000 is one of the best
> SQL books ever published. Any chance of a 2005 edition?
> --
> Jon Robertson
> Borland Certified Advanced Delphi 7 Developer
> MedEvolve, Inc
> http://www.medevolve.com
>|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uaLyYwD7FHA.736@.TK2MSFTNGP09.phx.gbl...
> And, in SQL Server 2005, you have to use the WITH keyword, which is
> optional in 2000
> SELECT ... FROM my_indexed_view WITH (NOEXPAND)
>
Well, you don't really have to use WITH here.
BOL:
In SQL Server 2005, with some exceptions, table hints are supported in the
FROM clause only when the hints are specified with the WITH keyword. Table
hints also must be specified by using parentheses.
The table hints allowed with and without the WITH keyword are the following:
NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE,
READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT,
READPAST, XLOCK, and NOEXPAND. When these table hints are specified without
the WITH keyword, the hints must be specified alone. For example: FROM t
(fastfirstrow).
David