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.

• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

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