What are implicit conversions

An implicit conversion is when SQL Server has to convert from one data type to another data type to be able to make a comparison.

Generally, implicit conversions are most problematic when they turn what could have been an index seek into an index scan. Implicit conversion can also affect cardinality estimates.

Often this is a issue that can be fixed relatively easily.

Sample Data

This sample code creates a table with 1,000,000 rows.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

create table dbo.MyImplicitConversionExample ( field1 varchar(10) not null primary key );

with BaseData as (
	select field1
	from (values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j')) as t(field1)
)
insert into dbo.MyImplicitConversionExample(field1) 
select
	concat(d1.field1, d2.field1, d3.field1, d4.field1, d5.field1, d6.field1) as field1
from
	BaseData as d1
	cross join BaseData as d2
	cross join BaseData as d3
	cross join BaseData as d4
	cross join BaseData as d5
	cross join BaseData as d6;

Demo of the Problem

Here field1 and ‘jahbah’ are both varchar values so no data type conversion is needed, and if we view the actual plan we see it did an index seek.

1

select field1 from dbo.MyImplicitConversionExample where field1 = 'jahbah';

Now let’s change the prior query ever so slightly and make ‘jahbah’ an nvarchar value. This time we see an index scan. On a million row table, we probably care that it is an index scan when we are just trying to return one row.

1

select field1 from dbo.MyImplicitConversionExample where field1 = N'jahbah';

Notice the warning symbol on the SELECT operator. Hovering over it reveals the message:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(10),[MyDatabase].[dbo].[MyImplicitConversionExample].[field1],0)=[@1]) may affect “SeekPlan” in query plan choice

Now let’s change the prior query to include an explicit conversion, and we get an index seek. This would be one way to fix the implicit conversion problem.

1

select field1 from dbo.MyImplicitConversionExample where field1 = cast(N'jahbah' as varchar(10));

Finding instances of implicit conversion

• You may note it in a query plan you are reviewing perhaps because you were looking at queries with high read counts.
• Capture the sqlserver.plan_affecting_convert event using an Extended Events session.
• Query the DMV sys.dm_exec_query_stats, cross apply sys.dm_exec_query_plan(plan_handle), and look for instances where query_plan like '%CONVERT_IMPLICIT%'.