Tweeted twitter.com/StackDBAs/status/753413845381775364
2 added 43 characters in body
source | link
  SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = 5

enter image description hereenter 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.

  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.

  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.

1
source | link

How to avoid implicit conversion for an Integer column

  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.