(You can follow the code examples in this article by using one of the AdventureWorks sample databases. We are using AdventureWorks2008R2 but any version should work. Download the sample databases from CODEPLEX.)
Implicit Conversions
Beginning in June of 2011 I wrote a series of articles discussing practical optimization techniques for a variety of SQL Server performance issues, from indexing fundamentals to deadlocks.
Read the first article in the series
However, there is one important performance issue I did not include. That is implicit conversion of data types by the SQL query engine.
What is an Implicit Conversion?
You probably perform explicit data type conversions in the SQL code you write using the CAST() or CONVERT() functions. These are necessary to get the results you need. Normally the performance issues, if any, are understood.
However, sometimes SQL Server performs implicit conversions that are almost completely invisible to you.
One of the more common reasons this happens is that SQL Server isn’t able to compare values of different data types. First it must convert one of the values to the same type as the other.
For example, in the HumanResources.Employee table we are using, the NationalIDNumber column is an nvarchar(15) even though it only contains strings of numeric digits. Since the data type is not obvious by just looking at the data, we might imagine that users frequently query the column using a simple integer search argument, like this:
- use AdventureWorks2008R2;
- select *
- from HumanResources.Employee
- where NationalIDNumber = 381073001;
In their infinite wisdom, the designers of SQL Server decided that they would not throw an error when this sort of a data type mismatch occured. Since the intent seems to be obvious, they would just convert the column values to an integer. I am not sure I agree with them, but that is what happens.
If you click the ‘Show Actual Query Plan’ toolbar button in Management Studio and then execute the query, you can see in the query plan that a ‘convert_implicit’ operation was performed on the column data, converting the values from NationalIDNumber from an nvarchar(15) data type to an integer.
Why Should You Care?
You will also notice that, even though there is an index on NationalIDNumber, an index scan was performed, not the less costly index seek that we would expect. This is caused by the need to first convert the column values. Instead of accessing one data page, the whole index was read.
In a small table like this, It is not a noticeable problem. However, on a large table this could cause a serious performance problem.
Of course, the fix for this is to make sure the data types of your search predicates match the data type of the column they address. But sometimes this is not as easy as it seems.
Implicit Conversions Caused by Your Application
Often the database drivers for your development platform will translate the SQL queries you write into Prepared SQL statements, separating the query from the parameters it uses. Many of those database drivers default to sending string parameters as double-byte Unicode (nvarchar) values regardless of the data type of the column that is referenced.
If you are using the default value for this setting and the string values in your database are varchar values, then you will have implicit conversions for every string comparison that happens in your application. That can be a real show-stopper.
Let’s change our example a little to demonstrate what happens when you provide a parameter of the wrong data type to a query:
- use AdventureWorks2008R2;
- declare @natnum varchar(15)
- set @natnum = ‘381073001’;
- select *
- from HumanResources.Employee
- where NationalIDNumber = @natnum;
If you view the execution plan, you will see the convert_implicit. This time it is in the ‘Seek Predicates’ section. We also see that, in this case, we happen to get an index seek, but still, the query will not perform very well.
Fixing the Problem
The fix for implicit conversions caused by your application’s database drivers can be very simple. If you use varchar data types in your database then you need to change the default settings in your application to send parameter strings as ASCII. The setting is often called just that, SendStringsAsASCII.
Conclusion
This article is not all you need to know about implicit conversions, but it is all that will fit into this month’s newsletter.
You need to be aware of implicit conversions in your applications, especially those coming from the parameterization of SQL code in your application. Use ‘Show Actual Query Plan’ to find out what is happening in your queries.
As a personal anecdote, when I first ran across this problem many years ago, the problem was not widely recognized. I wrote it up in an article on SQL_SERVER_PERFORMANCE.com. ( Long before I started this newsletter.)
I was flooded by email from all over the world, thanking me for pointing out this problem. Many people who had been tearing their hair out trying to find the cause of their poor performance reported overall system performance improved dramatically after they changed the Unicode/ASCII setting.