Tell me more ×
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.
up vote -3 down vote favorite

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

up vote 7 down vote

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.

share|improve this answer
My sql server version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Enterprise Edition (64-bit). When i use with (noexpand) sql server use views indexes and execute query faster. – DeeRain Sep 13 '12 at 10:49
1  
@DeeRain - 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." – Damien_The_Unbeliever Sep 13 '12 at 10:57
+1 Good catch @Damien_The_Unbeliever – brian Sep 13 '12 at 13:58
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 indexed view directly. – Paul White Sep 14 '12 at 11:39

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.