As documented in other Q&A on this site, if you create an indexed view you may have to give the noexpand hint to use it as such. Without the hint, cheaper versions of MSSQL will always treat it as an ordinary view, and even the 'enterprise' versions may still need the hint in practice.

But noexpand is not valid on an ordinary, not-indexed view. That means if you change a view from indexed to ordinary, queries stop working. You can't fully hide the implementation detail of whether it's an indexed view or not, unless you make another wrapper view.

Is there some hint that means the same thing as noexpand when applied to an indexed view, but is a no-op when applied to an ordinary view?

select name from myview (noexpand_if_necessary) where id = 55

Then if myview is an indexed view you get the benefit of the indexes; if myview happens to be an ordinary view the query happens as if the hint were not given.

I'm currently using MSSQL 2008 R2 but I would be interested in answers for newer MSSQL versions too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.