-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)

Paul White
  • 83,961
  • 28
  • 402
  • 634
DeeRain
  • 127
  • 3

1 Answers1

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Damien_The_Unbeliever
  • 637
  • 4
  • 7
  • In addition to the answer, it is not only limited to Enterprise edition but it applies also to developer edition. – Zerotoinfinity Nov 12 '14 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 '14 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 '20 at 15:01