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
    OrderId     =   r.OrderId                   
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , XCount      =   COUNT_BIG(*)
from dbo.Order r
group by r.OrderId


1 Answer 1


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 propagate inwards to all outer queries will automatically have NOEXPAND.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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