OPTIMIZE FOR variable error 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?
It is because you are declaring your @test variable as nvarchar(max) and comparing that to a non lob literal in the OPTIMIZE FOR clause.

From the docs on OPTIMIZE FOR :

OPTIMIZE FOR ( _@variable\_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&lt;>Fiddle



NVARCHAR(MAX)  and OPTIMIZE FOR 

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

When creating 4000 spaces:

SELECT REPLICATE(' ', 4000);


And adding them to the end of the 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.

You cannot use CAST() or CONVERT() to explicitly change the constant to NVARCHAR(MAX)
DB&lt;>Fiddle

Extra



If the variable is lower than nvarchar(max) but the constant is over 4000 (for nvarchar) characters, it still works.:

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

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

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

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


Because all the spaces after the literal value are 'cut off' and it is treated as a nvarchar(3):

&lt;ColumnReference Column="@test" ParameterCompiledValue="N'one'" ParameterRuntimeValue="N'one'" /&gt;


If we change the variable to nvarchar(4)

DECLARE @test nvarchar(4)


One more space is present in the ParameterCompiledValue

&lt;ColumnReference Column="@test" ParameterCompiledValue="N'one '" ParameterRuntimeValue="N'one'" /&gt;