-3

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)

2
  • 7
    As a minimum, you should include a sample query. Without that, one can only guess. Sep 13, 2012 at 10:38
  • Need more details. View definition, table definition, actual execution plan, when statistics were last updated on the underlying table. Sep 13, 2012 at 12:55

1 Answer 1

12

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.

3
  • In addition to the answer, it is not only limited to Enterprise edition but it applies also to developer edition. Nov 12, 2014 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. Nov 12, 2014 at 15:34
  • In my case I am using Enterprise edition, but it is still not using the indexed view. NOEXPAND did the trick, though. My query time went down to 0 seconds with it.
    – Eric
    Feb 13, 2020 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.