What Is Implicit Conversion In SQL Server

This is yet another quick post of simple changes that you can make to your code so as to create more optimal execution plans. This one is on implicit conversions. An implicit conversion is when SQL Server must automatically convert a data type from one type into another when comparing values, moving data, or combining values with other values. When these values are converted, during the query process, it adds additional overhead and impacts performance.

Here is a great chart by Microsoft that shows conversions and which will cause an implicit or explicit conversion. In this post, I will not go into explicit, just know that is what you explicitly tell SQL Server to CAST or CONVERT a value.

Image credit: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017

Let’s look at a very simple but common implicit conversion scenario. Here, we have a table Employee with a NationalIDNumber column defined with a NVARCHAR data type. In this query, we will use a WHERE clause to search for a specific ID.

In the query below, we have requested NationalIDNumber equal to the integer value 14417807. For SQL Server to compare these two data types, it must convert that NVARCHAR into INT. Which means every value in that column must go through a conversion process which causes a table scan.

USE AdventureWorks2016CTP3
GO
SET STATISTICS IO ON
GO
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807

In the execution plan, you will see an exclamation point warning you that there is a potential issue with the query. Hovering over the SELECT operator, you will see that a CONVERT_IMPLICIT is happening which may have affected the optimizer from using a SEEK.

 

(1 row affected)

Table 'Employee'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now the question is how do we fix it. It’s really simple but it does require a code change. Let’s look back at our query.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807

Remember we asked for an integer value. Just by adding single quotes to the value, we can eliminate our issue. It’s important to always know what data types your columns are when querying them. In this case, since it is an NVARCHAR, all I need to do is to supply a character value. This is accomplished by adding single quotes around the value.

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate,JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = '14417807'

It’s simple to see the results. Note above the Scan count 1, logical reads 9, physical reads 0. When we rerun it we get the below.

(1 row affected)

Table 'Employee'. Scan count 0, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can also see in the graphical plan that the warning is now gone, and we have a SEEK instead of the SCAN which is much more efficient.

The only thing left to clean up in this plan is the key lookup. To fix that take a look at my blog from last week here. There are many ways you can end up with implicit or explicit conversion issues and the additional overhead they can create. In most cases they are extremely easy to fix with a little code change, this was just one example. To help you find these in your environment Jonathan Kehayias has written a query to find column-side implicit conversions in your plan cache. Be sure to check it out.