Esat Erkec

Implicit conversion in SQL Server

January 22, 2019 by

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.

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (including nvarchar(max) )
  26. nchar
  27. varchar (including varchar(max) )
  28. char
  29. varbinary (including varbinary(max) )
  30. 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.

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.

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.

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.

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.

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.

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.

We will start the extended event.

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.

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.

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.

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

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
Esat Erkec
682 Views