By OliverRadini


2019-07-12 07:28:58 8 Comments

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?

1 comments

@Randi Vertongen 2019-07-12 07:55:39

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 ( [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


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 2019-07-12 07:59:05

Anything up to 8000 bytes (so, nvarchar(4000)) should be fine

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] Investigating errors from strange query

1 Answered Questions

[SOLVED] SQL server Stored Procedure temp variable value mismatching sometime

  • 2017-04-14 10:01:50
  • Tufan Chand
  • 214 View
  • 1 Score
  • 1 Answer
  • Tags:   sql-server tempdb

4 Answered Questions

[SOLVED] Choosing the right algorithm in HashBytes function

2 Answered Questions

1 Answered Questions

[SOLVED] SQL 2005 Unused procedures

2 Answered Questions

[SOLVED] Optimize select on subquery with COALESCE(…)

4 Answered Questions

[SOLVED] Extracting a field from RESTORE HEADERONLY

1 Answered Questions

[SOLVED] Script to query multiple instances

  • 2012-04-18 19:51:09
  • Alan Whited
  • 5525 View
  • 3 Score
  • 1 Answer
  • Tags:   sql-server t-sql

2 Answered Questions

[SOLVED] 'Conversion failed' error with ORDER BY CASE expression

Sponsored Content