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.