What are implicit conversions in SQL Server?

114

In this post I show a few examples of implicit conversions in SQL Server and the impact they can have on query performance.

Types of conversions in SQL Server

If you’re here, you most likely know what a data type conversion is, but, in short, it’s the operation of converting a value from one data type to another.

There are two types of conversions in SQL Server:

• explicit conversion – which is done by explicitly applying the CAST and CONVERT functions on a column, variable, or value.
• implicit conversion – when CAST and CONVERT are not used explicitly, but SQL Server ends up doing the conversation behind the scenes due to two distinct data types being compared.

The Data type conversion Microsoft Learn article does a great job of illustrating explicit and implicit conversions allowed in SQL Server.

Examples of implicit conversions

All the examples in this post use the HumanResources.Employee table in the AdventureWorks2019 database, hosted on SQL Server 2019 with the latest CU.

For reference this is the table’s structure.

But I’m focusing on the BusinessEntityID and NationalIDNumber columns.

And these are the indexes on the HumanResources.Employee table.
I’ve highlighted the indexes relevant to these examples

I’ll also be setting STATISTICS IO to ON in order to see how many pages the database engine ends up reading.

The implicit conversion that doesn’t really matter

I start with this one because it causes a false warning in the execution plan that I’m not a fan of.

The following query will force SQL Server to do an implicit conversion on the NationalIDNumber column, from NVARCHAR to INT, in order to match the BusinessEntityID column’s data type.

The above query returns this result.

And produces this execution plan.

Notice that the filtering operation is an Index Seek that accurately estimates the number of records to return.

And SQL Server only reads 2 8KB pages to retrieve this record.

(1 row affected)
Table ‘Employee’. Scan count 0, logical reads 2, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob page server reads 0,
lob read-ahead reads 0, lob page server read-ahead reads 0.

That warning is even easier to read in the XML version of the execution plan.

Now, I know that the warning states that it “may affect” cardinality estimates, but there’s no valid reason for this case of implicit conversion to ever affect cardinality estimates, because it’s not taking place in the WHERE clause or in a JOIN.

The implicit conversion that actually hurts performance

In this case, the following query forces SQL Server to do an implicit conversion on the NationalIDNumber column, from NVARCHAR to INT, so that it matches the data type of the value it’s being compared against.

I’m skipping the result set because it’s not that relevant.
But the execution plan looks like this.

In this case there are multiple warnings, about cardinality estimates as well as for the inability to perform a seek operation.
The result being the database engine reading the entire nonclustered index in order to return the required record.

And hovering over the Index Scan operator reveals what’s happening.

(1 row affected)
Table ‘Employee’. Scan count 1, logical reads 4, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob page server reads 0,
lob read-ahead reads 0, lob page server read-ahead reads 0.

I know that 4 8KB pages isn’t that impressive (the table only has 290 records), but the logic is the same regardless of table size.

The full list of warnings is easier to read in the XML version of the plan.

From a performance point of view, there is no difference between that query and this one.

The implicit conversion that looks like it could hurt performance, but doesn’t

This next query is fairly similar.
It compares a column of one data type to a value that’s a different data type.

Again, the result set isn’t that relevant.

But the execution plan might not be what you’d expect.

Everything is just perfect for this type of query.
There are no implicit conversion warnings, we have a seek on the clustered index.

So…. where did the implicit conversion go?

Hovering over the Clustered Index Seek plan operator reveals something interesting.

The implicit conversion still takes place, but it’s applied to the value that gets compared to the BusinessEntityID column instead of being applied to the column itself.
This, in turn, allows the database engine to use the clustered index for a seek to that particular record, instead of having to read the whole table like in the previous example.

This ends up reading only 2 8KB pages from the index.

(1 row affected)
Table ‘Employee’. Scan count 0, logical reads 2, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob page server reads 0,
lob read-ahead reads 0, lob page server read-ahead reads 0.

Not all implicit conversions are created equal

At this point, you might be wondering why did SQL Server opt to convert the column in one example and the value in the other example. And that’s a fair question.
It all has to do with data type precedence, this dictates which data type gets converted in the case of an implicit conversion.

With the data type precedence in mind, let’s break down the comparisons involved in the second and third example.

Example	Column name and data type	Value data type	Precedence
2	NationalIDNumber NVARCHAR(15)	INT	INT takes precedence over NVARCHAR
3	BusinessEntityID INT	NVARCHAR	INT takes precedence over NVARCHAR

A more subtle yet common scenario

Up-converting VARCHAR to NVARCHAR

Look, I know I said I’ll stick to the HumanResources.Employee table, but I figured I would cram in another example.

For this example I’m using the Sales.SalesOrderHeader table and adding the following index:

And the query looks like this.

In this case, the CreditCardApprovalCode is VARCHAR while the value is NVARCHAR (as indicated by the N preceding the string delimiter).

Since NVARCHAR takes precedence over VARCHAR, we end up with the implicit conversion impacting performance.

And the scan does end up reading the whole table (203 8KB pages).

(1 row affected)
Table ‘SalesOrderHeader’. Scan count 1, logical reads 203, physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

The fix for implicit conversion

It’s pretty simple when you have control over the code, just make sure that the data types of the values, variables, or other columns, match the ones of the columns you’re comparing them against.

But how can you fix implicit conversions in SQL Server when you can’t change the code? That’s a topic for another post.

Conclusion

Implicit conversions in SQL Server aren’t that tricky once you get the logic behind them.
They’re fairly easy to avoid as long as you’re mindful of the data types involved.

Ideally, you’d want to have a 1:1 match of the data types you’re filtering and comparing against. And don’t rely on the fact that some implicit conversions don’t have such impact on performance.