2

SQL Server 2014 Compatability level 110 uses old Cardinality Estimator. I am trying to enforce new CE for a statement. I am getting error "Incorrect syntax near the keyword 'option'." I need to do deletions in batches.

while 
(   
    Select top (1) 1
    from [AdventureWorks].dbo.DatabaseLog
    option (querytraceon 2312)  --error
) > 0
begin
    delete top (10000 )
    from [AdventureWorks].dbo.DatabaseLog
end

-- it runs ok by itself:

Select top (1) 1
from [AdventureWorks].[Person].[Person]
option (querytraceon 2312) 
0
2

The option clause can only be used on standalone SELECT statements.

Not where the SELECT is being used as an expression.

The following examples don't allow you to specify a query hint either.

DECLARE @I INT = (SELECT 1 OPTION (RECOMPILE))

IF (1 = (SELECT 1 OPTION (RECOMPILE))
    PRINT '1';

So you will either need to

  • restructure your code so that the statement you are trying to apply a query hint to is not directly embedded inline in the WHILE (would require either repeating the statement or wrapping it in a non inline function)
  • set the trace flag on at the session level
  • Use a plan guide (potentially - I haven't tested this)

For the specific example in your question you don't need the SELECT at all.

DECLARE @Dummy VARCHAR(100) ='setting initial rowcount'

WHILE @@rowcount > 0
  BEGIN
      DELETE TOP (10000 ) FROM [AdventureWorks].dbo.DatabaseLog;
  END 

or (as this will end up deleting all rows anyway).

TRUNCATE TABLE [AdventureWorks].dbo.DatabaseLog;
1

You are doing it wrong ... Below is the correct way of doing

if exists 
(   
    Select  1
    from [AdventureWorks2012].[Person].[Person]

)
begin
    Select  top 1 1
    from [AdventureWorks2012].[Person].[Person]
    option (querytraceon 2312) -- Use NEW CE Ref: https://support.microsoft.com/en-us/kb/2801413

end
else 
begin
   waitfor delay '00:00:05'
end
3
  • The first select statement, in exist, needs to run with option – ob213 Oct 2 '16 at 15:15
  • 1
    You cant run that. What you can do is to have dbcc traceon (2312) .. run your tsql and then dbcc traceoff(2312). HTH. – Kin Shah Oct 2 '16 at 19:11
  • So I can't use WHILE and OPTION. – ob213 Oct 2 '16 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.