Blog Pages

Indexed Views does not using the index of the view

Problem:
Indexed Views does not using the index of the view...

Solution:
Add WITH (NOEXPAND) to the select (after the view):
FROM dbo.indexedViewName iv WITH (NOEXPAND)

But why the optimizer didn't use the index of the view without this hint?
If a query contains references to columns that are present both in an indexed view and base tables, it can happen....

Note: in order to use NOEXPAND, those settings must set to be ON: 
  • ANSI_NULLS
  • QUOTED_IDENTIFIER
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
  • ANSI_PADDING
  • ARITHABORT

No comments:

Post a Comment