4
  SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = 5

enter image description here

I get convert_implicit() function called by SQL Server after executing the query in Actual Execution Plan.

  SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = convert(int,5)

enter image description here

Obviously when I explicitly convert the Scalar value to Integer type, convert_implicit() function is not called. Is there any way we can avoid implicit conversion for a scalar int value without using the explicit conversion. Also I would like to know the performance difference between implicit conversion ,explicit conversion and no conversion happened at all.

10

How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5; 

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5 
      AND 1=1
0

Community wiki answer:

Use a variable, you will also get the benefits of query reuse

Declare @ BeID int = 5
select xxx where BuisnessEntityID = @BeID

If you want to be certain you need to strongly type the query, either by using a variable or by using cast/convert:

select xxx where BuisnessEntityID = CAST (5 as int)

I think the performance overhead of the implicit conversion there is negligible - but there I might be wrong. But by using a variable you will definitely get the benefits of query cache reuse.

  • After seeing you comment, I've just realized that as long as I actually use the variables when setting the queries (in the ORM), I do not actually get this issue - while, when I would take the already-built query and just paste it into the SQL manager, for purpose of debugging it and checking the execution plan, then I don't use the variables/parameters and hence I get a false positive report that this query really does the implicit conversion. So, apparently, there may be no problem at all. – userfuser Apr 8 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.