For the last two weeks we have looked at why SQL Implicit Conversions (ICs) are bad and how to identify a query containing them.  This week we will look at a few ways to correct a query that contains an IC.

Here is the query we will be working with:

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[OrganizationNode]

      ,[OrganizationLevel]

  FROM [HumanResources].[Employee]

  WHERE NationalIDNumber = 879342154

GO

The first thing to notice is the field NationalIDNumber is not surrounded with single quotes, which means SQL will think that the value being passed is a numeric value.  If we look at the table HumanResources we see that the column Employee is a nvarchar data type.  Using Microsofts chart on data type conversions (https://msdn.microsoft.com/en-us/library/ms191530.aspx) we will see that this will cause an implicit conversion.

The first way we can change this is to surround the value in the WHERE statement with single quotes to tell SQL that this is a string value and not numeric.  Make sure to turn on Include Actual Execution Plan.

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[OrganizationNode]

      ,[OrganizationLevel]

  FROM [HumanResources].[Employee]

  WHERE NationalIDNumber = ‘879342154’

GO

When we run this query a number of things become apparent.  First, the yellow warning sign has gone way.

Next, in the Execution Plan window one part of the query has gone from an Index Scan to now an Index Seek.  This means that SQL is now using an index and not doing a full table scan to find the data (less IO).

We can also look at the IO differences between a full table scan and an Index Seek.  It shows that indeed an Index Seek is much, much more efficient in terms of IO.

There is also another way to remove the IC from the execution plan.  When SQL runs a query it gives that query a plan signature and puts that signature in to memory.  When queries run SQL will check these signatures against what is being run and if they match SQL can reuse the existing plan signature and not have to generate a new one.  So why is this important?  If we use the above query as our example and pretend that the column NationalIDNumber is not just numbers but also string values.  When a query come through that uses a string value such as WHERE NationalIDNumber = ‘TextExample’ SQL will consider this different from our original query (WHERE NationalIDNumber =879342154) and will generate a new plan signature.  How can we avoid this?  Use parameters.  Parameterized queries better match, and reuse existing cached plans.  Let’s change our query to use a parameter now by first creating the parameter and then setting it to a specified value.

DECLARE @ID nvarchar(15)

SET @ID = ‘879342154’

–Now we run the query

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[OrganizationNode]

      ,[OrganizationLevel]

   FROM [HumanResources].[Employee]

  WHERE NationalIDNumber = @ID

So now we have identified what implicit conversions are, why they are bad, how to identify them and finally a few ways to improve your query to remove them.  I hope this helps.