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.


CREATE TABLE ProductCategory
(
     CategoryID INT Primary Key,
     CategoryName VARCHAR(50)
);
CREATE TABLE Product
(
     ProductID INT Primary Key,
     ProductName VARCHAR(100),
     QtyInStock INT,
     CategoryID VARCHAR(10)
) ;

I’m going to insert data into the tables. Note that I am using the syntax to insert multiple rows into a table at one time, which is only available in SQL Server 2008 and higher.

INSERT INTO ProductCategory (CategoryID, CategoryName)
VALUES (1, 'Ale'),
(2, 'Lager'),
(3, 'N/A');

INSERT INTO Product (ProductID, ProductName, QtyInStock, CategoryID)
VALUES (1, 'Spotted Cow', 8966, '1'),
(2, 'Fat Squirrel', 7643, '1'),
(3, 'Moon Man', 339, '1'),
(4, 'Two Women', 1224, '2'),
(5, 'Home Town Blonde', 564, '2'),
(6, 'Ginger Ale', 899, '3');

This query will join the two tables on the CategoryID field.

DECLARE @Category INT
SELECT @Category = 2

SELECT PC.CategoryName,
     P.ProductID,
     P.ProductName
FROM ProductCategory PC
     INNER JOIN Product P ON P.CategoryID = PC.CategoryID
WHERE PC.CategoryID = @Category;

Reviewing the query execution plan shows CONVERT_IMPLICIT on Product.CategoryID.

Using a CAST in the FROM clause will remove this from the query plan.

SELECT PC.CategoryName,
     P.ProductID,
     P.ProductName
FROM ProductCategory PC
     INNER JOIN Product P ON P.CategoryID  = CAST(PC.CategoryID AS INT)
WHERE PC.CategoryID = @Category;

Implicit Conversion in the WHERE Clause

In this query, my variable is an INT and I am comparing it to a VARCHAR(10).

DECLARE @Category INT
SELECT @Category = 1

SELECT P.ProductName,
     P.QtyInStock
FROM Product P
WHERE P.CategoryID = @Category;

This also results in a CONVERT_IMPLICIT being performed.

Modifying the variable to be of the same data type as the field, a VARCHAR(10), resolves this problem.

DECLARE @Category VARCHAR(10)
SELECT @Category = '1'

SELECT P.ProductName,
     P.QtyInStock
FROM Product P
WHERE P.CategoryID = @Category;

Think Like the Optimizer

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.

↑ Back to top
  1. 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?

  2. 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.

  3. 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.

    I went looking and found what I was thinking this post was going to cover has been done and blogged about by Jonathan Kehayias already: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

    Figured I’d pass it along in case anyone else was thinking the same thing.

    • Hey Aaron,

      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 :)

        • Thanks Aaron! It can be hard to find cases of implicit conversion, unless you spot a problem query. It definitely drives home the point that good database design up front is essential!

  4. @Justin Dearing, because the INT datatype has a higher precendence than VARCHAR – in implicit conversions VARCHAR will always be converted to INT instead of the opposite. Details here: http://msdn.microsoft.com/en-us/library/ms190309.aspx

  5. 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.

  6. I liked the article. Excellent tip!

  7. 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!

  8. Nice article – also enjoyed the New Glarus plug!

  9. Jes,

    Enjoyed your post! I do have a question though.

    DECLARE @Category2 INT
    SELECT @Category2 = 1
    SELECT P.ProductName, P.QtyInStock FROM Product P WHERE P.CategoryID = @Category2;

    DECLARE @Category3 VARCHAR(10)
    SELECT @Category3 = ‘1’ SELECT P.ProductName, P.QtyInStock FROM Product P WHERE P.CategoryID = @Category3;

    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?

    Thanks!

    • Matt:

      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.

  10. I enjoyed the post also, and I also have a couple of questions.

    First:
    Realizing that it does make a difference, can anyone explain (or point me to an explanation) of why it makes a difference? In both cases a conversion is taking place.

    Both
    SELECT PC.CategoryName,
    P.ProductID,
    P.ProductName
    FROM ProductCategory PC
    INNER JOIN Product P ON P.CategoryID = PC.CategoryID
    WHERE PC.CategoryID = @Category;

    And
    SELECT PC.CategoryName,
    P.ProductID,
    P.ProductName
    FROM ProductCategory PC
    INNER JOIN Product P ON P.CategoryID = CAST(PC.CategoryID AS INT)
    WHERE PC.CategoryID = @Category;

    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?

  11. Pingback: Something for the Weekend - SQL Server Links 03/08/12

  12. 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:

    http://www.nobhillsoft.com/dianalite.aspx

    Simply load all the entities and compile them, watch for conversion warnings.
    we’re giving it for free now just to get your feedback. no catches, no gimmicks

    Thanks
    Jonathan Scion

  13. Jes,
    Good Article. Regarding the CAST, I believe you wanted to perform CAST(PC.CategoryID AS VARCHAR(10)) instead of CAST(PC.CategoryID AS INT) since PC.CategoryId is already defined as an INT.

    Hope you won’t mind me attempting to answer some other questions here.

    @SomewhereSomehow

    An explicit conversion can help performance significantly in certain cases. For eg, If you have a million rows in the Product table and an index exists on the CategoryID column, then

    SELECT PC.CategoryName,
    P.ProductID,
    P.ProductName
    FROM ProductCategory PC
    INNER JOIN Product P ON P.CategoryID = CAST(PC.CategoryID AS VARCHAR(10))
    WHERE PC.CategoryID = @Category;

    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.

  14. Love the New Glarus you snuck in there!

    Great examples, thank you.

  15. Pingback: Deadlock due to Implicit Conversion « sqlindian – blog Uncommitted

  16. Hi,

    The following article tackles the problem at its source: http://www.sqlservercentral.com/articles/Admin/65138/

    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.

    Additionally, you can use the following utility to search queries/plans that have implicit conversions:
    http://www.sqlservercentral.com/articles/Performance+Tuning/66729/

    Thanks
    Ian

  17. Pingback: Is BETWEEN faster than GTE and LTE? » Technobabble by Klee

  18. Pingback: Exam 70-464 | Simon Learning SQL Server

  19. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php