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.

Last week I wrote about the dangers of Implicit Conversions (ICs) in SQL query code (https://justinsetliffe.com/2015/04/10/implicit-conversions-2/).  Just to summarize, there are numerous reasons why they are bad.

This week let’s find out how to identify these ICs, it’s pretty easy to accomplish.

The first way is done when you have a query you want to run in SSMS.  For this example we will run a query against Microsoft’s AdventureWorks database.  This is a play/test database that can be downloaded from Microsoft (http://msftdbprodsamples.codeplex.com/).  Here is the query we will run for this:

SELECT [BusinessEntityID]

      ,[NationalIDNumber]

      ,[LoginID]

      ,[OrganizationNode]

      ,[OrganizationLevel]

      ,[JobTitle]

      ,[BirthDate]

      ,[MaritalStatus]

      ,[Gender]

      ,[HireDate]

      ,[SalariedFlag]

      ,[VacationHours]

      ,[SickLeaveHours]

      ,[CurrentFlag]

      ,[rowguid]

      ,[ModifiedDate]

  FROM [HumanResources].[Employee]

  WHERE NationalIDNumber = 879342154

GO

Make sure to select ‘Include Actual Execution Plan’ from the Query tab in SSMS, and execute the query.  Once you query runs you’ll notice a new tab called ‘Execution plan.

If you click on that tab you’ll notice a big ‘!’ inside of an yellow triangle.  That is telling you something is wrong with your query.  Inside of the execution plan window for this query you’ll notice an index scan, which means the index for that table is not being used (if there ever was an index).

From my first article we saw that ICs can cause a table scan so that is a great place to see what is going on.  Mousing over that box shows that indeed there is an implicit conversion happening.

Now, is there an easier way to identify ICs that are happening on your database?  Yes, there is a query that Jonathan Kehayias (of SQLSkills fame) wrote.  Just one FYI for this query, if you run this against a large database it can take a while.  Here is a link to his query – http://www.sqlskills.com/blogs/jonathan/finding-implicit-column-conversions-in-the-plan-cache/.

So far we have identified why ICs are bad in a query and how to find them, so what can we do next week?  I think finding ways to fix them is now called for.  After all you want your queries to be as efficient (I love that word by the way) as possible, it means more free time for you and less calls from your clients with complaints.

So the gauntlet has been thrown down by Sir Brent Ozar and his merry band of Supra-Genius Ultimate’s. Make a blog he says. Pick a topic he says. Well as a famous philosopher once said “Go do it right now. Seriously, you have nothing better to do. You’re reading my blog, for crying out loud.” Ok, it was that Brent guy again.

He’s right so here we go.  What to write about for the first blog…..??? I know, something near and dear to my pain. Implicit Conversions and SQL. Insert screaming at random intervals. So, what exactly are implcit conversions and why are they bad?

1) Per Microsoft – Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

2) Can result in increased CPU usage for query (bad).

3) Can cause the query to do a table scan instead of a seek (bad SQL, very bad).

4) Can lead to the DBA getting an increase in the number of “My app is running slow” calls from their clients.

Microsoft even has a URL with a nice little picture of all the data types and which ones can create implicit conversions when used together –https://msdn.microsoft.com/en-us/library/ms191530.aspx.

Next week, how to identify said implicit conversions……or maybe something else. Only the Shadow knows….I may know also.

UPDATE – Jonathan Kehayias of SQL Skills has an updated mapping of data types and what data type matchings cause either scans or seeks.  Please use this going forward, thanks you.

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/