SQL 2008 R2 Standard - Index View Support

Many years ago (almost ten) I thought Index View is more like an enterprise edition (SQL 2000) only feature but I was wrong and Index view were indtroducted in SQL 2000 to satifsy competivive product's support for materialize view.

However, You can still create index view and physically materialize that view in all the edition of开发者_StackOverflow SQL 2000/2005 and query will use that index on a view if you specify NOEXPAND query hint (which is not needed in enterprise/developer editon)

Here is the white paper on Index View (that confirm what I said earlier) http://msdn.microsoft.com/en-us/library/dd171921.aspx

However, it appears to me that starting with SQL 2008/R2 index view indeed is an enterprise edition feature.

I did compare feature by different edition http://msdn.microsoft.com/en-us/library/cc645993.aspx

so in SQL 2008 R2 Standard edition you are able to create index view but looks like NOEXPAND hint will not work so it is almost useless...

Is it possible to create index view and use that index (instead of index on base table) in SQL Server 2008 R2 (standard or express edition) using noexpand hint?

This other article on SQLServerCentral seems to suggest that yes, NOEXPAND continues to work perfectly on every edition of SQL Server from 2005 to 2012. I'll quote:

"Then NOEXPAND hint still works in non-Enterprise editions of SQL Server. I think there has been some confusion as to what this hint actually does. It forces the query optimizer to rely on the view, rather than the underlying table, for optimization. It does not force the query optimizer to use any given index on a view.

The sites I found online stating that NOEXPAND does not work did not include any testing methodology, so I can't say why it did not work for them.I can say that it can work in situations where the query optimizer decides the index is useful."