Handling Implicit Conversion

Implicit conversion occurs when SQL Server needs to automatically handle type mismatches between data. SQL Server does this by converting a data type to match. What do data types get converted from and to? So glad you asked as Microsoft has a chart showing just that:

Why is implicit conversion a problem? There is a performance hit for the conversion to take place and as we’ll see in a query plan later on, cardinality estimates can be impacted.

Implicit Conversion Example

Let’s go through an example of a query with implicit conversion.

We’ll create a generic table and insert a few test records:

USE ExampleDB;
GO

CREATE TABLE Emp (
	ID INT IDENTITY(1, 1) PRIMARY KEY
	,LastName VARCHAR(50) NULL
	,SSN CHAR(9) NULL
	);
GO

INSERT INTO Emp
VALUES ('Callihan','123456789')
	,('Doe','112233445')
	,('Smith','008675309');
GO

Now, let’s run a query looking for a specific SSN and include the actual execution plan. When we created our table, the SSN was created as CHAR(9). When the query to search our Emp table is being considered, perhaps we’re thinking “a SSN is always numbers, so no need for single quotes.” For that reason here, instead of searching for a CHAR type, we’ll just use the integer values:

SELECT * FROM Emp
WHERE SSN = 123456789;
GO

We get our record back in the Results tab. Looks fine so far but if we check our execution plan, we’ll see a yellow exclamation point indicating an issue. If we hover, we’ll see more details:

We have an implicit conversion issue. If we run the same query but add single quotes around our SSN so that we’re comparing CHAR and CHAR:

SELECT * FROM Emp
WHERE SSN = '123456789';
GO

We’ll see that the yellow exclamation point warning is gone.

Checking for Implicit Conversion

How do you find out if you have implicit conversion happening on your SQL Server? One easy way is to run sp_BlitzCache which is part of the Brent Ozar First Responder Kit. Running sp_BlitzCache in this example, we’ll see the following:

Another way would be to run this type of query:

SELECT DB_NAME(qp.dbid) AS 'Database'
	,qs.creation_time
	,qs.last_execution_time
	,st.text
	,qp.query_plan
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE CAST(query_plan AS VARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%')
GO

With either option, we can click into the query plan and see where we have issues:

Whether you are creating new tables or developing queries to run against existing tables, make sure you’re carefully considering the proper data types. It will make a difference.

Thanks for reading!