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:

SQL Server Implicit Conversion_1

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:

SQL Server Implicit Conversion_2

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!

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server Implicit Conversion”

  1. A couple of corrections.

    This:
    “Are SQL Server Implicit Conversions expensive? Yes, they are!”
    Should be:
    “Are SQL Server Implicit Conversions expensive? Yes, they might be!”

    This:
    “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.”
    Should be:
    “Not only can a SQL Server Implicit Conversion entail extra CPU or IO costs, it can fail completely when one data type cannot be converted to another. Implicit conversions are designed to avoid loosing precision.”

Leave a Reply to Bill Cancel reply

Your email address will not be published. Required fields are marked *