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;
GO

CREATE TABLE dbo.Users_Varchar (Id INT PRIMARY KEY CLUSTERED, Location VARCHAR(100));
INSERT INTO dbo.Users_Varchar (Id, Location)
  SELECT Id, Location
  FROM dbo.Users;
GO

CREATE INDEX Location ON dbo.Users_Varchar(Location);
GO

Let’s find all users from Colombia. To prove a point, let’s query the dbo.Users_Varchar table instead of the original dbo.Users 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;
GO

Notice we have declared @Location as NVARCHAR. We have a type mismatch between the column and the variable.

Let’s see the execution plan.

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

Let’s 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 order. This is a short version:

1. datetimeoffset
2. datetime2
3. datetime
4. smalldatetime
5. date
6. time
7. decimal
8. bigint
9. int
10. timestamp
11. uniqueidentifier
12. nvarchar (including nvarchar(max))
13. varchar (including varchar(max))

SQL Server convert “lower” types to “higher” types. We don’t need to memorize this order. Let’s remember SQL Server always has to convert VARCHAR to other types.

For the complete list of type precedence between all data types, check Microsoft docs on SQL Server data Type precedence.

An implicit conversion that seeks

In our example, the VARCHAR type was on the column, on the left side of the comparison in the WHERE. It means SQL Server had to read the whole content of the index to convert and then compare. That’s 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;
GO

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

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, we can use the third query from these six performance tuning tips from Pinal Dave. But, after taking Brent Ozar’s Mastering courses, I learned to start working with the most expensive queries instead of jumping to queries with implicit convertion issues right away.

Voilà! Those are implicit conversions and why you should care. Let’s use input parameters with the right data types on your queries and store procedures. Otherwise, we will pay the performance penalty of converting and comparing types. Implicit conversions are like functions around columns, implicitly added by SQL Server itself.

Let’s remember that not all implicit conversions are bad. When looking at execution plans, let’s check how many rows SQL Server reads to convert and compare things.

For more content on SQL Server, check how to compare datetimes without the time part, how to write case-sensitive searches and how to optimize queries with GROUP BY.

Happy coding!