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