OPTIMIZE FOR variable error

Question

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?

Answer 1

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<>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<>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):

<ColumnReference Column="@test" ParameterCompiledValue="N'one'" ParameterRuntimeValue="N'one'" />

If we change the variable to nvarchar(4)

DECLARE @test nvarchar(4)

One more space is present in the ParameterCompiledValue

<ColumnReference Column="@test" ParameterCompiledValue="N'one '" ParameterRuntimeValue="N'one'" />

• Chris Rolliston: Anything up to 8000 bytes (so, nvarchar(4000)) should be fine