Views and Hints

  • NicHopper

    SSCrazy Eights

    Points: 8852

    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

  • Phil Parkin

    SSC Guru

    Points: 243382

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • NicHopper

    SSCrazy Eights

    Points: 8852

    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

  • Phil Parkin

    SSC Guru

    Points: 243382

    NicHopper wrote:

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply