This article will provide an overview of SQL Server implicit conversion including data type precedence and conversion tables, implicit conversion examples and means to detect occurrences of implicit conversion
SQL Server query performance issues are one of the most challenging processes for database administrators. In some cases, query performance can cause huge problems and it might affect the whole SQL Server instance performance. That is why every database administrator has some knowledge about query performance and troubleshooting methodologies.
Certainly, there are lots of factors and reasons which affect the query performance. However, the main principle or technique to solve these types of problems is based on well understanding and interpreting of the SQL execution plan of query. The SQL Execution plans show us series of steps which are taken during query execution, so we can uncover and find out any performance problems related to query. After all this brief information about query performance issues, we will discuss and learn the details of this kind of topic which affects query performance which is named as implicit conversion.
In some cases, we can combine two different data types in a join condition or we can compare two different data types in the where clause. In these cases, SQL Server tries to convert one data type to another during the query execution process. This data conversion process is referred to as Implicit Conversion because this type of conversion is made in behind of scenes by the SQL Server Query Optimizer and, as such, the process is abstracted from users. However, we can notice this type of conversion in the execution plan of the query.
Data type precedence
Before we start discussing implicit conversion, we will review the concept of data type precedence in SQL Server. As we have already noted, the miss-matched data types have to be converted to compatible formats by SQL Server and this data type conversion is also done according to a defined process governed by precedence. The data type precedence rule specifies which data type is converted to the other. The data types have precedence order for SQL Server and the lower precedence data type is converted to the higher precedence data type.
- user-defined data types (highest)
- sql_variant
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max) )
- nchar
- varchar (including varchar(max) )
- char
- varbinary (including varbinary(max) )
- binary (lowest)
Examples
In the following query, we will compare two columns which have different datatypes so we will prove the methodology of precedence data type conversion rule.
In the first step, we will create a single column table and this column data type is integer and we will populate some data to this table.
|
1 2 3 4 5 6 7 |
DROP TABLE IF EXISTS TestPre CREATE TABLE TestPre (NumericCol INT) INSERT INTO TestPre VALUES (1), (2), (3) |
Now, we will execute the following query and interpret the execution plan of this query and also don’t forget to activate actual execution plan before executing the query.
|
1 |
SELECT * FROM TestPre WHERE NumericCol =N'1' |
As you can see the above image, the query optimizer converts the textual data type to an integer because INT data type precedence is higher than NVARCHAR.
Data type conversion
The following summary conversion chart shows that which data type can be implicitly converted to another one.
Conversion limitations
However, according to this chart, all possible data conversions cannot be made by SQL Server such. When we look at the following sample, SQL Server does not convert textual data types to integers. The output of this query will be an error.
|
1 |
SELECT * FROM TestPre WHERE NumericCol =N'A' |
In addition, if you require this type of conversion, you can use TRY_PARSE or TRY_CONVERT functions so that you can overcome this type of errors during the query execution.
Note: You can find all details in the Ben Richardson’s Understanding SQL Server’s TRY_PARSE and TRY_CONVERT functions article.
Detecting implicit conversions
Now, we will explore how to interpret execution plans which include implicit conversion. We will execute the following query in the Wide World Importers sample database and examine the actual execution plan.
|
1 2 3 4 |
SELECT TransactionDate ,IsFinalized FROM [Sales].[CustomerTransactions] where IsFinalized LIKE '1%' |
As you can see in the above image, there is a warning sign in the select operator and it indicates that there is a problem in the execution plan. When we hover over the mouse icon in the Select operator, the detail screen will appear and we can find out the select operator details. The warning details clearly tell us the reason of the warning sign is implicit conversion. If we examine the detail of the execution plan, a wild card operator (%) is used for bit column and that is a problem because a bit column only takes two values and these are 1 (true) or 0 (false) so it does not make any sense to use ‘1%’ expression for bit column. When we go through the execution plan details, it makes the following conversion and this conversion purpose is convert textual data to bit data for this reason it takes the first character of the textual data.
|
1 |
CONVERT_IMPLICIT(varchar(1),[WideWorldImporters].[Sales].[CustomerTransactions].[IsFinalized],0)>='1' |
In addition, we are seeing another detail in the select operator which is about “CardinalityEstimate”. The task of Cardinality Estimator is to determine how many rows will be returned from the query and so this estimation directly affects the ability to choose the proper index. However, in this query, incorrect data type conversion directly affects the used index in the query.
Let’s avoid the implicit conversion for this query. The solution is very basic because we will only convert the “IsFinalized LIKE ‘1%’’” expression to “IsFinalized = 1” so that the query will return the same result set and we will avoid the implicit conversion affects in the query.
|
1 2 3 4 |
SELECT TransactionDate ,IsFinalized FROM [Sales].[CustomerTransactions] where IsFinalized = 1 |
As you can see in the above image, there isn’t any warning sign in the execution plan and also the index scan operator has changed the index seek operator. This index seek operator directly improves the query performance because the index seek operator is more selective than the index scan operator. At this point I want to add a notice about some details about implicit conversion. The conversion processes don’t change the query plan so they don’t affect query performance. Therefore, these type of data conversions are considered acceptable processes, in the context of managing performance.
In addition, we can detect implicit conversion issues in our database by the help of Extended Events. The plan_affecting_convert event captures queries whose data type conversion does affect the query execution plan. Now, we will create a new extended event through the following script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE EVENT SESSION [ImplicitConversionCapture] ON SERVER ADD EVENT sqlserver.plan_affecting_convert( ACTION(sqlserver.database_name) WHERE ([sqlserver].[database_name]=N'WideWorldImporters')), ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.database_name) WHERE ([sqlserver].[database_name]=N'WideWorldImporters')), ADD EVENT sqlserver.sql_batch_starting( ACTION(sqlserver.database_name) WHERE ([sqlserver].[database_name]=N'WideWorldImporters')) ADD TARGET package0.event_file(SET filename=N'ImplicitConversion') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO |
We will start the extended event.
|
1 |
ALTER EVENT SESSION [ImplicitConversionCapture] ON SERVER STATE= START |
In this step, we will re-execute the following query which causes implicit conversion and then analyze the data which is captured by our extended event.
|
1 2 3 4 |
SELECT TransactionDate ,IsFinalized FROM [Sales].[CustomerTransactions] where IsFinalized LIKE '1%' |
The above image shows that the plan_affecting_convert event occurred due to prior query which was executed.
Another option is DMVs (Dynamic Management Views) which is used to detect implicit conversion. Through the following query, we can detect the implicit conversion issued queries.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT DB_NAME(sql_text.[dbid]) AS DatabaseName, sql_text.text AS [Query Text], query_stats.execution_count AS [Execution Count], execution_plan.query_plan AS [Query Plan] FROM sys.dm_exec_query_stats AS query_stats WITH (NOLOCK) CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS execution_plan WHERE CAST(query_plan AS VARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%') AND DB_NAME(sql_text.[dbid])='WideWorldImporters' AND CAST(query_plan AS VARCHAR(MAX)) NOT LIKE '%CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS sql_text%' |
Conclusion
In this article, we discussed implicit conversion details, related performance implications and also about how to detect related issues with help of extended events and dynamic management views.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023