How Do Implicit Type Conversions Affect SQL Queries?

Let’s have a look at implicit type conversions and how they can affect SQL Server’s performance.

What are type conversions?

Let’s start with the basics. What are data type conversions? (If you’re well-versed in this, feel free to skip ahead a bit.)

SQL Server works with different types of data. It works with numbers, character strings, dates, binaries, and the like. And even those broad classifications have nuances. For example, numbers can be DECIMAL, NUMERIC, FLOAT, REAL, BIGINT, TINYINT, INT, etc.

Likewise, character strings can be fixed length or variable length. You can have unicode and non-unicode strings.

When you ask SQL Server to move, combine, or compare data between different objects, those objects need to be the same type so SQL Server will know how to handle the data.

For example, what happens when you try to add the number 1 with a character string of ‘1’?

Those are two different types of data. One is an integer and the other is a string.

SQL Server needs to get both objects to be of the same type, either both will be characters and the answer will be ’11’. Or both will be integers and the result will be 2.

There are rules that govern how the data is converted. And we can influence those rules by explicitly converting one of the objects to a different type using the CAST() or CONVERT() functions.

For more information about converting data types in SQL Server, check out Data type conversion (Database Engine) – SQL Server | Microsoft Learn.

When does SQL Server do implicit type conversions?

If we don’t explicitly tell SQL Server how we wish the type conversion to be handled, it will often automatically do the work for us. This is known as implicit type conversions. In the earlier example of combining the number 1 and the character string ‘1’, SQL Server will automatically, or implicitly, make the conversion for us.

Adding the number 1 and the character string ‘1’ results in 2. SQL Server performed the conversion of the character ‘1’ to an integer for us.

That’s handy. Right?

Well, yes. And sometimes no.

Why should we care about implicit type conversions?

Implicit type conversions can make life easier for us as we write TSQL code. We can let SQL Server do those pesky conversions for us. We can code without worrying about data types. But is that a good idea?

A query without an implicit type conversion

Let’s look at a slightly more realistic example than the 1 + ‘1’ example. An example that uses data. I’m going to use my BaseballData database for this example, but the concept applies everywhere. (For more information on the baseball example database, see Data! Getcha Data Here!)

In the code example below, I’m declaring a variable @id as VARCHAR(10). In the WHERE clause, I’m comparing the value of @id to the PlayerId column. The PlayerId column is also VARCHAR(10). So they match.

USE BaseballData ; 

DECLARE @id VARCHAR(10) = 'aaronha01' ; 

SELECT  PlayerId, 
        nameLast, 
        nameFirst,
        birthCountry
FROM    dbo.players
WHERE   PlayerId = @id ;

Looking at the Query Plan, we see that SQL Server chose to resolve the query using a nonclustered index. It performed an index seek to go directly to the row(s) needed and did a Key Lookup to get the data not included in the index.

The index seek with a key lookup is the best way to resolve this query since we’re looking for one specific row. In other queries, index seeks with key lookup may not be the optimal plan. I’m not making a generalized statement that index seeks with key lookups are always good.

A query with an implicit type conversion

Now, let’s see what happens if we have a data type mismatch in the query. For this example, let’s change the @id variable to NVARCHAR(10). The new query is:

USE BaseballData ; 

DECLARE @id NVARCHAR(10) = 'aaronha01' ; 

SELECT  PlayerId, 
        nameLast, 
        nameFirst,
        birthCountry
FROM    dbo.players
WHERE   PlayerId = @id ;

The results are are below. The query plan looks very similar. However, if you look at little closer, the index seek has been replaced with an index scan. SQL Server is scanning the entire index to look for all rows that match what we’ve specified in the WHERE clause. And it still does the key lookup

That may not be so bad if the index is narrow and there aren’t a lot of rows. However, for a very large table with a wide index, that could be a lot more work for SQL Server.

Notice the warning in the final select icon, the one in the upper left hand corner of the plan. Looking at the popup window, we can see that SQL Server (via Azure Data Studio) warns us about the implicit type conversion, reporting that a CONVERT_IMPLICIT was required to resolve the query.

So, what is SQL Server doing?

When SQL Server performs an implicit type conversion, it is converting one of the objects so that it matches the other one, comparing apples to apples so to speak.

We can see this effect by slightly rewriting the query to do an explicit type conversion.

USE BaseballData ; 

DECLARE @id NVARCHAR(10) = 'aaronha01' ; 

SELECT  PlayerId, 
        nameLast, 
        nameFirst,
        birthCountry
FROM    dbo.players
WHERE   CONVERT(NVARCHAR(10), PlayerId) = @id  ;

The change from an index seek to an index scan can dramatically affect performance in some cases.

What about…

Here are a few common questions about implicit type conversions.

• Ok, so implicit type conversions can be bad, but what makes them costly?
  As in the final example, implicit type conversions essentially place the column you’re referencing inside a function. That prevents the query optimizer from being able to use a seek operation. It knows the index will be helpful, so it does the next best thing and scans the index.
• What about VARCHAR(10) and VARCHAR(100)? Will that cause an implicit type conversion?
  No. Only the length of the variable length string is changing. The data type is the same, VARCHAR, so no conversion is needed.
• Are there limits to what SQL Server will implicitly convert?
  Sure. Have a look at the chart in the following link. It shows what data types will be implicitly converted, which can be explicitly converted, and which conversions are not allowed at all. Data type conversion (Database Engine) – SQL Server | Microsoft Learn
• How can I avoid implicit type conversions?
  When declaring a variable or parameter that will be used in a WHERE clause of a query / stored procedure, ensure it matches the column it will be compared to.
  

See also

For some additional performance enhancing tips, check out these posts:

• How do Views Affect SQL Server Performance?
• Identify Disk I/O Performance Issues for Your SQL Server Using DiskSpd
• SQL Server Performance and Windows Power Plan
• How Many tempdb Data Files Should My SQL Server Have?

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations improve the performance of their SQL Servers? It’s easy and there is no obligation. Let’s talk.