SQL Query Hint WITH (NOEXPAND)

If you've reached to the point that you optimize your queries with indexed views, you probably know what you're doing or at least willing to try and understand execution plans.


Indexed views are great (as long as you don't abuse them), they can be an aggregate which is always ready to be used, you can index the columns of the view and get overall huge performance boost.

create view dbo.vwView with schemabinding
as
  select ...

But, if you don't use SQL Enterprise edition, the query optimizer needs to be told explicitly to use them, otherwise it will treat the indexed views like any other views, get the underlying query and use it instead. which defeats the purpose of having an indexed view.


So, what do we do? add with (noexpand) to queries that might benefit from the indexed view's indexes, note that you shouldn't use it blindly, look at the execution plans for both options and base your decision on that.

select ... from dbo.vwView with (noexpand)

Tags: