SQL SERVER – Applying Query Hints to Views

I totally agree that it is not possible to apply query hints to views inside the view. It will give you an error. However, there is a workaround.

If you try to use the query hints inside the view it will give you errors such as the following.

Msg 156, Level 15, State 1, Procedure Customers, Line 32 [Batch Start Line 9]
Incorrect syntax near the keyword ‘OPTION’.

However, if you want to use a query hint, I suggest that you use it outside the view on the SELECT statement.

For example, if you want to force a join hint on your query, I suggest that you use the following syntax.

SELECT *
FROM [Website].[Customers]
OPTION (LOOP JOIN)

When you use option hint inside the view, it will give you an error but if you use it outside the view, you will be able to use it successfully. On the same note, I must say that I do not like query hints and I have made a view on the same topic:

Well, just watch the video and let me know what you think of it.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Menu
Exit mobile version