CODEWITHSUNDEEP
sql-serverquery-optimization
Sky Ricer
Sky Ricer

Reputation: 21

NVARCHAR and VARCHAR conversion cause index scan

declare @p0 nvarchar(4000)
select id_t_s from table1 where id_t = @p0

This will be index scan, because id_t column is varchar(100). it will takes145 ms. There is a warning from execution plan.

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(100),tables1.[id_t],0)=[@P0]) may affect "SeekPlan" in query plan choice.

If I change it to below:

declare @p0 nvarchar(4000)
select id_t_s from table1 where id_t = @p0

This will be index scan, because id_t column is varchar(100). took 0 ms.

declare @p0 varchar(4000)
select id_t_s from table1 where id_t = @p0

This will be index seek. It will takes 0 ms.

Is there any way to fix it without change code or table?

Thanks, Sky

Upvotes: 2

Views: 1751

Answers (1)

O. Jones
O. Jones

Reputation: 108839

Try this WHERE clause.

WHERE id_t = CAST(@p0 AS VARCHAR(4000))

This will allow the server to compare the values in the column to data of the same type. That lets it do the index seek you want.

Consider defining your @p0 variable so it has the same data type as your table's column. As you have it, you're trying to look up a Unicode value in a 8-bit (ASCII, latin1) column. SQL server does it for you, but slower than you might like.

Upvotes: 3

Related Questions