OPTIMIZE FOR variable error

by OliverRadini   Last Updated July 12, 2019 08:06 AM

I'm having a problem specifying a value within an OPTIMIZE FOR statement. I'd like to optimize the query with the value as a string, but I must be doing something wrong, because SQL gives the following error:

The value specified for the variable "@test" in the OPTIMIZE FOR clause could not be implicitly converted to that variable's type.

The example below is slightly contrived but the problem is the same as the one I'm having with my actual query.

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test = 'one'))

What am I doing wrong?



Answers 1


It is because you are declaring your @test variable as nvarchar(max) and comparing that to a literal in the OPTIMIZE FOR clause.

From the docs on OPTIMIZE FOR :

OPTIMIZE FOR ( [email protected]\_name_ { UNKNOWN | = literal_constant } [ , ...n ] )

You should change the length of the variable to the size of the highest possible character length of the value that will be stored in it.

DECLARE @test nvarchar(3);

DB<>Fiddle

In theory you are able to optimize for nvarchar(max) fields, but it appears that it has to be over 8000 characters long:

When creating 8000 spaces:

SELECT REPLICATE(' ', 8000);

And adding them to the one constant:

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test  = N'one                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ' ))

It works too.

Randi Vertongen
Randi Vertongen
July 12, 2019 07:55 AM

Related Questions


Updated June 24, 2016 08:02 AM

Updated October 12, 2017 04:06 AM

Updated June 16, 2015 11:02 AM

Updated July 26, 2015 17:02 PM