June 12, 2019 at 2:02 pm
Hi,
I wondered if anyone knew if you could use OPTION (USE HINT in views.
I have a view that when used with a TOP x command goes a little crazy so to mitigate this I want to add OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) to handle this.
It works perfectly when I add it as a select
for example of
Msg 156, Level 15, State 1, Procedure VwSampleView, Line 5 [Batch Start Line 0]
Incorrect syntax near the keyword 'OPTION'.
Msg 154, Level 15, State 1, Procedure VwSampleView, Line 5 [Batch Start Line 0]
a USE database statement is not allowed in a procedure, function or trigger.
SELECT TOP 10000 'abc' as a
FROM sys.tables
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))
But I need this to be a view so I tried
CREATE VIEW [dbo].[VwSampleView]
AS
SELECT TOP 10000 'abc' as a
FROM sys.tables
OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))
GO
But this results in the following error
Msg 156, Level 15, State 1, Procedure VwSampleView, Line 5 [Batch Start Line 0]
Incorrect syntax near the keyword 'OPTION'.
Msg 154, Level 15, State 1, Procedure VwSampleView, Line 5 [Batch Start Line 0]
a USE database statement is not allowed in a procedure, function or trigger.
So does any know if there is anything preventing the use of this in a view?
Thanks,
Nic
June 12, 2019 at 2:18 pm
You cannot use the OPTION clause in views. See this link.
The SELECT clauses in a view definition cannot include the following:
- An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
- The INTO keyword
- The OPTION clause
- A reference to a temporary table or a table variable.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 12, 2019 at 2:43 pm
Hi,
Thank for you confirming that, I did indeed search but wanted to clarify and see if anyone had a workaround.
Sorry for troubling you.
Have a good day.
Thanks,
Nic
June 12, 2019 at 2:50 pm
Hi, Thank for you confirming that, I did indeed search but wanted to clarify and see if anyone had a workaround. Sorry for troubling you. Have a good day. Thanks, Nic
No trouble.
Just to be clear, the 'please perform the search yourself' text in my signature is not directed at your post ... it appears in all my posts 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply