4

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 seems like length of input parameter is limited to varchar(8000). So once you change datatype of @test variable to VARCHAR. It works fine. – Rajesh Ranjan Jul 12 at 7:44
  • @RajeshRanjan Thank you for your input; presuming that I can't change the type of the variable, how might I solve the problem? – OliverRadini Jul 12 at 7:45
  • @RajeshRanjan on a related note, the query will still not work for me, with the same error, if I change @test for varchar – OliverRadini Jul 12 at 7:52
  • 1
    It works fine for me.... declare @TEMP table(asWord nvarchar(max), asNumber int) insert into @TEMP (asWord, asNumber) values ( 'one', 1 ), ( 'two', 2 ) DECLARE @test varchar(8000)--nvarchar(max) -----it works firn with varchar(8000)--But let say there's restriction to use VARCHAR(MAX) how would it work SET @test = 'one' select * from @TEMP where asWord = @test OPTION (OPTIMIZE FOR(@test = 'one')) – Rajesh Ranjan Jul 12 at 7:55
  • 1
    @MaxVernon It doesn't need to be nvarchar(max) fortunately; this was there for historic reasons, and I was happily able to add a limit. – OliverRadini Jul 12 at 12:18
5

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'" />
  • 1
    Anything up to 8000 bytes (so, nvarchar(4000)) should be fine – Chris Rolliston Jul 12 at 7:59

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.