I have created a view (dbo.twhinr910100) WITH SCHEMABINDING on a table (dbo.twhinr110100), and some indexes in the view to optimize performances.
Everything worked like a charm on TEST server and the execution plan was correct.
I did the exact same thing on PROD server but the indexes are ignored. Even adding the WITH(INDEX()) clause on the query has no effect, making a full scan on the original table, making the view completely unusable.
I need this to work as it is. Why PROD server is completely ignoring my indexes?
Thanks.
This type of error commonly occurs when the development/testing phases of work have happened on a box running Developer Edition (which is really Enterprise Edition just with different licensing) but is then deployed to a box using Standard Edition.
Some behaviours around indexed views are different between these Editions. Most notably, Standard Edition usually needs the NOEXPAND hint to be applied when querying the view in order for it to consider using the index.
If you need to present the view to an application which will be executing queries outside of your control, it may not be possible for the application to know to apply the NOEXPAND hint. What you can do, however, is create two views - an inner view which is your indexed view and an outer view which just queries the inner view and uses the NOEXPAND hint. All queries that are then using the outer view are able to benefit from the indexes on the inner view and don't have to specify the NOEXPAND hint themselves.
