Can I use an option hint in my view definition to make it run faster?
asked
Hints in view definition
Hints, sure... but not a query hint, as a view is just a stored SUB-query. It's a common misconception that a view is a stored query, and therefore should support things like an ORDER BY clause.
So, you can have table hints, such as NOLOCK, but not query hints such as RECOMPILE.
Although I agree with Rob's answer (and therefore voted for it), I want to clarify his point about ORDER BY. You can use an ORDER BY in a view definition if you also use the TOP clause. However, even then the exact order of results is not guaranteed. The ORDER BY will only be used to determine the top results. To sort those results, the query referencing the view would still need to specify its own ORDER BY clause.
Yes, and with FOR XML too. But it's not the tradition usage of ORDER BY, and I hope will be replaced one day with TOP (N) OVER (ORDER BY...), and FOR XML OVER (ORDER BY...). Point well made though. I was definitely meaning for the ordering of results.
Excellent answer. One hint I have used several times inside a view is noexpand. I talked about that in more detail at
http://www.sqlservercentral.com/articles/Indexed+Views/63963/ . And of course since you mention NOLOCK, it is worth pointing out that using that one runs the risk of dirty reads which can cause problems.
Before you try to fix the slowness, you must understand the cause. Options are the last resort because they force the query optimizer to execute the query in a certain way. It might work for now but, if the data population changes, you're preventing the query optimizer from evolving a new plan. The chances are that there's a simple reason the query is slow so you should examine the query plan first. Look for the bits that show the highest percentage of the cost and also look at the parts where the lines are thickest as they are processing the most rows.
