Implicit data type conversion is one of those things you hardly think about or notice when writing your queries. But as you will soon find out while reading this article it is one of those small tweaks that can increase the performance of your query!

Implicit data type conversion happens when the SQL Server detects a use of a data type in your query which is different from the type you specified on your table. It’s an action SQL Server does automatically, and if it weren’t for the execution plan you wouldn’t even knew it happened.

Let’s set up a little test to show when and how implicit conversion works! For this test we will be using the AdventureWorks database.

This is a very simple query that will return an ID and a NationalIDNumber from the HumanResources.Employee table, make sure you enable the “Show Actual Execution Plan” option before running the query below:

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = 123456789;

Now that we got a result back let’s take a look at the execution plan:

Wow….an index scan while we even supplied the value we needed in the WHERE clause? Normally we would expect an index seek operation!

If we put our cursor on top of the index scan operation we can find the cause:

As you can see from the execution plan, SQL Server has run an implicit conversion on the value we supplied in the WHERE clause.

The reason for this is actually very simple and a mistake I see a lot of people making! The NationalIDNumber has a data type of VARCHAR but in the query we supplied an INTEGER to the WHERE clause because we didn’t put the number in the query between quotes to make it a string.

Let’s see what happens if we fix that little mistake but adding quotes:

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = '123456789';

Now let’s look at the execution plan:

Now that looks good again, a index seek operation, which is much cheaper to perform then a index scan and generate less IO operations then a index scan.

As you can see from this simple example it is very important to be consistent with your data types, it is the only way to avoid implicit conversions and it will increase the performance of your queries, especially in large tables. If you declare a column as a INT make sure you query it as a INT as well and avoid putting numeric values into columns declared as VARCHAR (which happens more times then I can count).