Implicit conversions are convenient. Implicit conversions are EVIL!
When talking to professionals about implicit conversions, I’ve heard a few different comments, such as:
- (a developer) Don’t waste time converting if the engine does it for you.
- (a DBA) Sure it probably adds a little bit of overhead but I’ve never found it to be severe enough to worry about.
Both of those people were completely missing the point. The developer was focused on development efficiency and assuming that the engine would perform the conversion in the most efficient manner possible. The DBA seemed most concerned with the CPU overhead of performing the conversion. Unfortunately, that is not the worst issue with using implicit conversions.
SARGability is the ability of the query to properly search the arguments that you pass it. Essentially it is whether an index can be effectively utilized with a given predicate. By effectively, I mean using index seeks instead of scans. We won’t get too deep into SARGability in general here but I will demonstrate how implicit conversions affect it. For other areas, please see these references.
- String Length and SARGability
- SARGability – Not Just for the WHERE Clause
- SARGability: Why %String% is slow
An implicit conversion occurs when one side of your predicate is not the identical data type as the other side of the predicate. Each data type has a precedence rank and lower data types will be converted to the one with the higher precedence. These can occur with any type of predicate such as column-to-column, column-to-variable, column-to-scalar-function, scalar-function-to-scalar-function, etc.
There is some overhead with performing a conversion but that is not going to be our focus. What is really important is where the implicit conversion occurs. Let’s demonstrate while we explain.
Click here to download the complete demonstration setup script. I omitted the table creation, population, and indexing for the sake of brevity.
Even though there is a precedence of data types, this only applies to data types which are compatible. Here we get a red error message from an improper conversion.
DECLARE @bigintParam BIGINT = 152 SELECT bigintNum, tinyintNum FROM #ImplicitConverts WHERE varcharString = @bigintParam
Now we will play with compatible data types and examine their performance.
DECLARE @tinyintParam TINYINT = 152 DECLARE @varcharParam VARCHAR(100) = '152' SELECT tinyintNum FROM #ImplicitConverts WHERE tinyintNum = @tinyintParam SELECT bigintNum FROM #ImplicitConverts WHERE bigintNum = @tinyintParam SELECT bigintNum FROM #ImplicitConverts WHERE bigintNum = @varcharParam
There are three interesting notes about the execution of these queries.
1. All three queries utilized index seeks.
2. The first query didn’t need to convert because the data types were a match but the second two queries did require an implicit conversion.
3. All three were very fast (less than 1/3rd of a second).
What is wrong with the implicit conversion then?
In the cases above, the implicit conversion occurred on the parameter side, not on the column side of the predicate. This meant that the queries were all SARGable, they could use the index to perform seeks. If you refer back to the data type precedence list, you will notice that BIGINT is #15, TINYINT is #18 and VARCHAR is #27. This means that, when comparing to a BIGINT, the TINYINT and VARCHAR will incur the implicit conversion and become a BIGINT that the index can effectively use for a seek.
When the implicit conversion occurs on the column side of the predicate, our land mines begin to explode.
This next demo uses an NVARCHAR (precedence #25) variable with a VARCHAR (precedence #27) column. Since the column is of a lower precedence than the variable, our implicit conversion will occur on the column and force an index scan.
DECLARE @nvarcharParam NVARCHAR(100) = N'7371varcharString' SELECT varcharString, nvarcharString FROM #ImplicitConverts WHERE varcharString = @nvarcharParam
28 seconds compared with less than 1/3rd of a second. That says it all.
The solution to this problem is simple. All you need to do is match your data types during development / design or utilize explicit conversions so that your conversion occurs on variables and not columns. The design aspect is particularly important when dealing with JOINs.
This CAST solves our issue from the previous query.
SELECT varcharString, nvarcharString FROM #ImplicitConverts WHERE varcharString = CAST(@nvarcharParam AS VARCHAR(100))