SQL Server Implicit Conversion

Hi Friends,

Are SQL Server Implicit Conversions expensive? Yes, they are!

Not only does a SQL Server Implicit Conversion takes extra CPU, it can also cause loss of precision or fail completely when one data type cannot be converted to another.

Here is a quick example:

Turn ON actual execution plan and execute the following query:

-- turn on actual execution plan
use AdventureWorks2012
go

set statistics TIME ON
go

select * from sales.SalesOrderHeader
where CreditCardApprovalCode = N'105041Vi84182'

select * from sales.SalesOrderHeader
where CreditCardApprovalCode = '105041Vi84182'

And let’s observe the Messages tab followed by Execution Plan:

Messages tab:

The first query takes 16 ms on CPU and the second one takes 0 ms. CreditCardApprovalCode column of SalesOrderHeader table is of type VARCHAR. And I purposely prefix ‘N’ to the constant in the WHERE clause denoting the value to be NVARCHAR. Now SQL Server has to perform and implicit conversion to match the two types which takes extra CPU cycles. For the other query, that’s not the case.

Let’s check Execution Plans:

The execution plan for the first query shows a warning sign!

Either you convert explicitly using CAST or CONVERT function or make sure you supply correct predicate values. What I mean by this is; in the above example there was no need to prefix ‘N’. If I have good knowledge of my data and their data types, I can avoid such pitfalls. More so, today we mostly deal with Unicode data because we will have mostly have text columns with NVARCHAR data type and thus, it has become a habit to prefix ‘N’ with text/character data not realizing that sometimes we may land up dealing with VARCHAR columns causing implicit conversion, unintentionally. For example, almost all text columns in all the tables in AdvanetureWorks2012 database are NVARCHAR, but to prove my point I had to find a column with type VARCHAR – and there are just a few, probably less than ten – that explains it all!