How to avoid implicit conversions in SQL server

Dream. Dare. Do – that is Suyati’s work principle in a nutshell.

Apr
06
2013
  • Author:
  • Ramesh Velayudhan

In SQL Server it is important to define and use correct data types for storage as well as for retrieval of data from the database tables using queries. If we fail to use correct data types in the design or in queries, it could lead to what is called IMPLICIT Conversion issue. This means extra work for SQL Server to change one data type to another while the columns are joined together in queries for  retrieving,  inserting, updating or deleting data from the tables. This can cause several serious issues like Indexes not being utilized when they exist, CPU usage above than the normal, data precision loss and a host of many other problems.

Normally the IMPLICIT Conversion problem occurs in two places: when the columns are JOINED on in the FROM Clause and in the WHERE clause. Let’s look how this happens.

Let’s start by setting up a table for testing purpose and with some serious amounts of data in it (100000 rows!)

table for testing purpose

The above table has a column ‘IntValue’ which holds character data but wrongly named to give the impression that it is meant for Integer data.  We will also add an index on this column to see how it gets used when we write queries against this table.

CREATE NONCLUSTERED INDEX IDX_ICTest_IntValue ON ICTest(IntValue)

Below is what happens when we try to query against this table using a string value for “IntValue” column.

string value for “IntValue”

 

As expected, the query is able to use the index defined on the column and it does not show any CONVERSION operator being used in the process (Pay attention to the “Seek Predicates” section in the above screen shot!).  Now we will run the same query by using an Integer value in the Search part.

Integer value in the Search part

It is evident that the Execution engine had to perform an implicit conversion operation before proceeding with the Search option and also it was forced to perform an Index Scan rather than a Seek slowing up the whole process!!

We will see the same behavior when we use columns with different data types in the JOIN clause as show below:

data types in the JOIN clause

 

Be aware of the probable conversion issues that can arise due to the improper usage of data types while designing tables and writing queries to retrieve the data from the tables. Hope this information would help you in writing better SQL Code! Thanks! 🙂

The following links delves deeper into this issue!

http://msdn.microsoft.com/en-us/library/ms191530.aspx

http://blogs.msdn.com/b/craigfr/archive/2008/06/05/implicit-conversions.aspx

Leave a Comment

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