Wednesday, December 16, 2009

Option (Recompile) inside of a "if exists" statement

Suppose you have a bad query plan of a query in "If Exists" statement.
One way to fix it is to tell to the SQL Server to recompile the query
every time the SP executed, by providing Option(Recompile) query hint.

But if you use the Option(Recompile) hint inside of a "If Exists"
statement, you will get a syntax error:

IF EXISTS ( SELECT 1
                  FROM dbo.SomeTable
                  WHERE SomeColumn=SomeValue
                  OPTION(Recompile)
                 )

PRINT 'Yes'
ELSE
PRINT 'NO'
-----------------------------------------------------------------------
Incorrect syntax near the keyword 'OPTION'.

I do not understand why,if you have an explanation,
please write it in the comments.

There is a workaround(provided by Erland Sommarskog)
for this issue:
DECLARE @Flag BIT
SELECT TOP(1) @Flag=1
FROM    dbo.SomeTable
WHERE SomeColumn=SomeValueOPTION(Recompile)
IF @Flag = 1
PRINT 'Yes'
ELSE
PRINT 'No'

1 comment:

  1. You can submit your request/bug here http://connect.microsoft.com/

    ReplyDelete