Blog

When you build a database in SQL Server, you need to pick a data type for each field in each table. Hopefully, a great database designer took the time to carefully understand business needs and what the field would be used for, and assigned the correct data type. When that doesn’t happen, you may run into problems down the road.

What is Implicit Conversion, and Why is it Bad For Me?

When SQL Server tries to join on or compare fields of different data types, if they are not the same data type, it will convert one to match the other. This is called implicit conversion.

Implicit conversion is not a desired action in SQL Server, and can lead to excessive CPU use. In some cases, converting from one type to another can cause a loss of precision. A chart showing all the data types and their conversions can be found at http://msdn.microsoft.com/en-us/library/ms187928.aspx.

The most common places you will see implicit conversions happening is in the FROM and WHERE clauses.

Implicit Conversion in the FROM Clause

Joining two tables on a field that has a different data type in each table will cause SQL Server to convert one type to the other.

Let’s look at an example, using two tables, ProductCategory and Product.

To get the best performance from your queries, you need to understand how the query optimizer works, and think like it. Preventing implicit conversion, and the resulting CPU usage, begins with proper database design. If you run into it after the database has been created, knowing how to spot it and work with it will result in a better-performing database.

In the case of WHERE P.CategoryID = @Category; why is the query planner converting P.CategoryId and not @Category? If its converted the latter, there would be no performance hit compared to manually conversion. Being the planner is pretty damn smart in recent versions of SQL server, why does it fail to make this obvious optimization?

Data Type Precedence – “When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence”

Implicit Conversion burned me pretty badly in the last few months just as you mentioned. Things were working great until a new execution plan was generated and SQL started implicitly converting every value in our index instead of our constant parameter value.

When I read the title of the post at first I thought this was going to show us how to identify implicit conversions happening in code in our DBs, but really it was just a nice primer on implicit conversions.

Just understand that methods using the query cache depend on plans being in the cache. Use of recompile or memory pressure on the cache may limit what you can identify that way.

I think Jes’ post is meant to show you how to identify it in a query execution plan, which you might capture in a variety of ways. (Different ways to skin the implicit conversion. That was a terrible pun!)

Good point about the plans being in the cache in order to catch implicit conversion that way.

I wasn’t trying to say that Jes’ post wasn’t valuable in any way in my first comment, just wanted to point out it made me think “is there a way to find the sneaky implicit conversions in existing code” since I didn’t know if there was a way and was excited to find one on the interwebz

Also, you don’t mention this in the article but implicit conversions will often force an index or table scan instead of a seek – since the engine doesn’t know if the values match until they are the same datatype, it needs to convert the entire column before comparison.

Hello!
Could you please clarify, what is the point of using explicit cast in from clause, except that it tells developer, that columns have different types. What is the benefit from the performance point of view?
In both cases there are seek+scan, the only difference is the order in join.
Thanks!

Running those two queries together, I was thinking that the query without the implicit conversion would have a lower query cost compared to the query with the implicit conversion. However, they both show a query cost of 50% each. How can this be?

Since you are comparing against a constant (a declared variable with a single value) instead of a field with many values, the conversion is a non-factor. It’s a one-time operation instead of an operation performed against every row.

End up converting PC.CategoryID to an int before making the comparison. So why does it matter to the compiler?

Second:
In the case of
SELECT PC.CategoryName,
P.ProductID,
P.ProductName
FROM ProductCategory PC
INNER JOIN Product P ON P.CategoryID = PC.CategoryID
WHERE PC.CategoryID = @Category;
It appears that the copy of PC.CategoryID being converted is the one in the WHERE clause. And in fact when I removed that line I no longer saw the term “CONVERT_IMPLICIT”. So then I tried doing my CAST on the version in the WHERE clause and again didn’t see CONVERT_IMPLICIT but I do see CONVERT instead (which I didn’t see when the convert is in the ON clause). Can you explain?

This good article pointed out implicit conversions in the WHERE and FROM, but they can happen in many, many other places (INSERT statements, passing parameters to a proc\function…) and there are many other conversions that could cause errors but the query execution plan wont show it (unicode to non-unicode, passing decimal values with different precision,etc)

We have been working very hard to find all those problems, and this free tool will find all those types of problems for you:

will be much faster than the version without CAST. The point here is, you want to prevent a scan on the larger table resulting from the implicit conversion and datatype precedence by explicitly converting the column in the smaller table.

@Matt

You are not seeing a difference because both the Plans use a scan. Try adding an index on the CategoryID column in the Product table and add few thousand rows to see the difference in the query cost.

@Kenneth

First – See response above. AS IS, it does not make a difference.
Second – When you don’t see a CONVERT_IMPLICIT in the scan operator’s predicate, look for the compute scalar operator. The CONVERT_IMLICT will be there as an expression.

Roji Thomas,
Thx for the answer, good point! Yes, in that way – it really makes sense. IMHO, it then should be paraphrased to smth like ‘using explicit cast to override implicit order of conversion’ – it will make the article point much more clear.

The utility described in the article will allow you to quickly identify which columns have mismatched datatypes across tables in a database, correcting these will improve performance, integrity and maintainability.

Hi even though i have no mismatch of columns,sqlserver still does an implicit conversion.And my collation is SQL_Latin1_General_CP1_CI_AS.
This is resulting in full table scan rather than index seek.
I have a non clustered index on a column.
I have also tried changing the collation to Latin1_General_CI_AS.But there was no difference.
Please suggest me code fix to solve this issue.

That’s a question I can’t answer without seeing the plan. Thankfully, there’s a website you can anonymize the query and upload it to. Using SQL Sentry’s Plan Explorer (download from http://sqlsentry.net/plan-explorer/sql-server-query-view.asp), upload the plan. Experts are standing by to answer your questions!