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 a view that has a clustered index along with other non-clustered indexes.

A simple SELECT query does not use any of the indexes even when I select an indexed column.

What could be the reason for this?

I'm using Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Enterprise Edition, on Windows NT 6.1 (Build 7600)

share|improve this question
6  
As a minimum, you should include a sample query. Without that, one can only guess. –  dezso Sep 13 '12 at 10:38
    
Need more details. View definition, table definition, actual execution plan, when statistics were last updated on the underlying table. –  Aaron Bertrand Sep 13 '12 at 12:55

1 Answer 1

Resolving indexes on Views:

In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used.

I'm guessing that you're on Standard Edition.

Maybe you're falling foul of other rules listed on that page, such as:

The estimated cost for using the index has the lowest cost of any access mechanisms considered by the query optimizer.


Like any query on a view, the reference to the view in the query is expanded into the view definition before optimization. If the optimizer finds a cheap enough way to satisfy the query before it considers matching the query tree to indexed views, you get a plan that accesses the base tables. WITH (NOEXPAND) is the only way to guarantee the optimizer produces a plan that accesses an index on a view directly.

share|improve this answer
    
In addition to the answer, it is not only limited to Enterprise edition but it applies also to developer edition. –  Zerotoinfinite Nov 12 at 15:26
1  
@Zerotoinfinite - technically correct, but only because Developer edition and Enterprise edition (and, I think, Evaluation edition) are all in fact exactly the same code. The only difference between them is the license. –  Damien_The_Unbeliever Nov 12 at 15:34

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.