noexpand hint for non-enterpise edition and performance

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)

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.

StackExchange Database Administrators Q#3044, answer score: 14