Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:

Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.

So will it work (I am talking about performance)

select * from dbo.OrderTotals with (noexpand, index=IXCU_OrderTotals)

on SQL Server Standard edition as well as it works

select * from dbo.OrderTotals

on the Enterprise one?

Here is code for view:

CREATE VIEW dbo.OrderTotals
WITH SCHEMABINDING
AS
select 
    OrderId     =   r.OrderId                   
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , XCount      =   COUNT_BIG(*)
from dbo.Order r
group by r.OrderId

CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId)
share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

The difference is that Enterprise edition without the hint may decide not to use the indexed view but the base tables instead.

My personal experience is that SQL Server is somewhat braindead in this. I almost always have to use the hint: the query is quicker with far less IO even though the plan "looks" worse with a scan on the view not index seeks on the base tables. And it runs more consistently too

YMMV of course :-)

So, to answer, it will (should?) work the same based on what I've seen. Other folk may have different experiences and I'm interested in other answers

To avoid using the hint everywhere you can wrap the indexed view in another view with the hint: hints propoagte inwards to all outer queries will automatically have NOEXPAND.

share|improve this answer
    
I got "Index hints cannot be specified within a schema-bound object" when tried to create outer view with schemabinding as select * from OrderTotals with(noexpand, index=IXCU_xxx). :) –  garik May 30 '11 at 13:36
2  
@garik: I'd just have NOEXPAND, not the index hint. There is only one index anyway: it's clustered so table scan = index scan –  gbn May 30 '11 at 13:38
    
no discussion. closed :) –  garik May 31 '11 at 13:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.