Geometric shapes made of wood

What are implicit conversions and why you should care

SQL Server compares columns and parameters with the same data types. But, if the two data types are different, weird things happen. Let’s see what implicit conversions are and why we should care.

An implicit conversion happens when the data types of columns and parameters in comparisons are different. And SQL Server has to convert between them, following type precedence rules. Often, implicit conversions lead to unneeded index or table scans.

An implicit conversion that scans

Let’s see an implicit convention. For this, let’s create a new table from the StackOverflow Users table. But, this time, let’s change the Location data type from NVARCHAR to VARCHAR.

USE StackOverflow2013;

INSERT INTO dbo.Users_Varchar (Id, Location)
  SELECT Id, Location
  FROM dbo.Users;

CREATE INDEX Location ON dbo.Users_Varchar(Location);

Let’s find all users from Colombia. To prove a point, let’s query the dbo.Users_Varchar table.

DECLARE @Location NVARCHAR(20) = N'Colombia';

SELECT Id, Location
FROM dbo.Users_Varchar
/* The column is VARCHAR, but the parameter NVARCHAR */
WHERE Location = @Location;

Notice we have used as a parameter an NVARCHAR variable. We have a type mismatch between the column and the variable.

Let’s see the execution plan.

Execution plan of finding all users StackOverflow users from Colombia
StackOverflow users from Colombia

SQL Server had to scan the index on Location. But, why?

Warning sign on execution plan of finding all users StackOverflow users from Colombia
Warning sign on SELECT operator

Notice the warning sign on the execution plan. When we hover over it, it shows the cause. SQL Server had to convert the two types. Yes, SQL Server converted between VARCHAR and NVARCHAR.

SQL Server data type precedence

To determine what types to convert SQL Server follows a data type precedence table. This is a short version.

Data Types
nvarchar (including nvarchar(max))
varchar (including varchar(max))

Lower types in the table convert to higher ones. You don’t need to memorize it. Remember, SQL Server always has to convert VARCHAR to other types.

For the complete list, check Microsoft docs on SQL Server data Type precedence.

An implicit conversion that seeks

In our example, the VARCHAR type was on the left of the comparison in the WHERE. It means SQL Server had to read the whole content of the index to convert and then compare. More than 2 million rows. That’s why the index scan.

Let’s use the original dbo.Users table with Location as NVARCHAR and repeat the query. This time, switching the variable type to VARCHAR. What would be different?

DECLARE @Location VARCHAR(20) = 'Colombia';

SELECT Id, Location
/* We're filtering on the original Users table */
FROM dbo.Users
/* This time, the column is NVARCHAR, but the parameter VARCHAR */
WHERE Location = @Location;

Now, the VARCHAR type is on the right of the comparison. It means SQL Server has to do one single conversion. The parameter.

Execution plan of finding all users StackOverflow users from Colombia
Index Seek on Location index when finding all users from Colombia

This time we don’t have a yellow bang on our execution plan. And, we have an Index Seek. Not all implicit conversions are bad.

In stored procedures and queries, use input parameters with the same types as the columns on the tables.

To identify which queries on your SQL Server have implicit conversions issues, use the third query from six performance tuning tips. But, remember not all implicit conversions are bad. Check how many rows SQL Server reads to convert and compare things.

Voilà! Those are implicit conversions and why you should care. Remember to use input parameters with the right data types on your queries and store procedures. Otherwise, you will pay the performance penalty of converting and comparing types.

For more content on SQL Server, check how to compare datetimes without the time part and don’t write functions in WHERE clauses.

Happy coding!