How implicit data type conversion slows down your query

Implicit data type conversion is one of those things you hardly think about or notice when writing your queries. But as you will soon find out while reading this article it is one of those small tweaks that can increase the performance of your query!

Implicit data type conversion happens when the SQL Server detects a use of a data type in your query which is different from the type you specified on your table. It’s an action SQL Server does automatically, and if it weren’t for the execution plan you wouldn’t even knew it happened.

Let’s set up a little test to show when and how implicit conversion works! For this test we will be using the AdventureWorks database.

This is a very simple query that will return an ID and a NationalIDNumber from the HumanResources.Employee table, make sure you enable the “Show Actual Execution Plan” option before running the query below:

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = 123456789;

Now that we got a result back let’s take a look at the execution plan:

Implic_01

Wow….an index scan while we even supplied the value we needed in the WHERE clause? Normally we would expect an index seek operation!

If we put our cursor on top of the index scan operation we can find the cause:

As you can see from the execution plan, SQL Server has run an implicit conversion on the value we supplied in the WHERE clause.

The reason for this is actually very simple and a mistake I see a lot of people making! The NationalIDNumber has a data type of VARCHAR but in the query we supplied an INTEGER to the WHERE clause because we didn’t put the number in the query between quotes to make it a string.

Let’s see what happens if we fix that little mistake but adding quotes:

Implic_02

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = '123456789';

Now let’s look at the execution plan:

Implic_03

Now that looks good again, a index seek operation, which is much cheaper to perform then a index scan and generate less IO operations then a index scan.

As you can see from this simple example it is very important to be consistent with your data types, it is the only way to avoid implicit conversions and it will increase the performance of your queries, especially in large tables. If you declare a column as a INT make sure you query it as a INT as well and avoid putting numeric values into columns declared as VARCHAR (which happens more times then I can count).

28 comments to “How implicit data type conversion slows down your query”

You can leave a reply or Trackback this post.
  1. I take pleasure in, lead to I discovered exactly what I was having a look for.
    You have ended my 4 day long hunt! God Bless you man. Have a great day.

    Bye

  2. I was curious if you ever considered changing the page layout
    of your site? Its very well written; I love what youve
    got to say. But maybe you could a little more in the way of content so people could connect with it better.
    Youve got an awful lot of text for only having one
    or two images. Maybe you could space it out better?

  3. Morestone Granite and Marble Limited is a Chinese manufacturer
    of stone countertop for hospitality and multifamily market, and also provides the
    FF&E procurement and logistics delivery service.
    We are familiar with several hotel brands such as Hampton Inn and Suites by Hilton, Country Inn and Suites by
    Radisson, SpringHill Suites by Marriott, Sleep Inn and Suites by Choice Hotel, Hawthorn Suites
    by Wyndham, Holiday Inn Express by IHG etc.

    Stone Countertop and Wood Cabinet for Hospitality and Multifamily
    //www.stonepluscabinet.com

  4. Good post. I learn something totally new and challenging
    on sites I stumbleupon everyday. It will always be helpful to read through content from
    other authors and use a little something from other
    web sites.

  5. Superb post however I was wondering if you could write a litte
    more on this topic? I’d be very thankful if you could elaborate a little bit more.
    Cheers!

  6. Thanks , I have recently been looking for information approximately this subject for a long time and yours is the greatest I
    have found out till now. But, what concerning the bottom line?
    Are you certain concerning the supply?

  7. It’s difficult to find experienced people about this subject,
    but you sound like you know what you’re talking about!

    Thanks

  8. Another thing I’ve really noticed is always that foor many people,
    less-than-perfect crediit is thee consequence of circumstanes outside of their control.
    Suuch ass they may bee actually saddled by having an illness so that they have
    high bills for collections. It would be due to a employent loss or maybe the inability to goo to work.
    Sometimes divorce proceedings can really send the finances
    in the unddsired direction. Thank you for sharing your thinking on this
    website.

  9. hey there and thanks for your information – I have
    certainly picked up something new from proper here.
    I did on the other hand expertise several technical poits the usage of thks web site, as I skilled to reload the
    web site many instances previous to I may jut get it to load correctly.
    I had beeen considering in case your web hosting is OK?
    Not that I’m complaining, but sluggish loading cases times will often impact your placement in google and can injury
    your high quality ranking if ads and ***********

Leave a Reply to Eloise Cancel reply

Your email address will not be published.