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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply