CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better?


Data type conversion has been one of the most used features of any programming language. Microsoft SQL Server users are familiar with the two functions: CAST() and CONVERT() and use them regularly, and more specifically, interchangeably. A few months ago, I had written a post comparing the performance of CAST v/s CONVERT, and was able to prove that there is no difference in performance of the two functions.You can read that post here: https://nakulvachhrajani.com/2011/01/03/cast-vs-convert/.

Recently, a colleague and I were discussing a particular design element and found that a data type casting was required. During this discussion, the following questions almost simultaneously came to our minds –

  • If the performance and end results are the same, what is the difference between CAST & CONVERT aside from the fact that they have different syntax elements?
  • Do we really need 2 data conversion functions?
Parameter CAST CONVERT
ANSI standard Yes No
Data-type coverage Limited Full (Date & Date Time values supported)
Performance No difference No difference
Microsoft SQL Server implementation ?? ??
Books On Line Page http://msdn.microsoft.com/en-us/library/ms187928.aspx http://msdn.microsoft.com/en-us/library/ms187928.aspx

So, I ran a little test, and today, I will share with you my findings.

The CAST v/s CONVERT Test

The test is quite simple – we have two identical T-SQL statements, one using CAST, the other using CONVERT. We will use the Properties window of SSMS to analyze the execution plan and try to see what can be found about the underlying implementation.

Let’s take a look at our test statements:

/***********************************************************
Press Ctrl+M (or go to Query->Include Actual Execution Plan)
************************************************************/
USE AdventureWorks2008R2
GO
--Use CONVERT
select CONVERT(VARCHAR(10),BusinessEntityID) FROM HumanResources.Employee

--Use CAST
select CAST(BusinessEntityID AS VARCHAR(10)) FROM HumanResources.Employee
  1. Connect to your SQL Server instance using SQL Server Management Studio (SSMS)
  2. Copy the T-SQL code above into SSMS Query editor window
  3. Run the above T-SQL statements against your SQL Server instance
  4. Change over to the Execution Plan tab in the Results pane
  5. In the results pane, notice that both CAST & CONVERT have been implemented as “Compute Scalar” operators
  6. image
  7. Press the F4 key or go to View –> Properties to launch the properties window
  8. Expand the “Defined Values” node

CONVERT implementation

CONVERT does not come up with any surprises, and has a straightforward internal implementation as demonstrated in the Properties window.

image

CAST implementation

When we move to the properties for the query using the CAST operation, we see that under the hood, SQL Server does take us for a ride. Internally, CAST is implemented as a CONVERT call. There is no difference between CAST & CONVERT besides the fact that CAST is an ANSI standard, while CONVERT is not. No wonder both CAST & CONVERT demonstrate the same performance.

image

Surprised? Try it for yourself – I was not prepared to see what I saw when I first ran through the test. I restarted the entire server and got a cup of coffee to make sure I was not dreaming!

Conclusion

I guess all I need to do now is to complete the little grid I had above:

Parameter CAST CONVERT
ANSI standard Yes No
Data-type coverage Limited Full (Date & Date Time values supported)
Performance No difference No difference
Microsoft SQL Server implementation CONVERT CONVERT
Books On Line Page http://msdn.microsoft.com/en-us/library/ms187928.aspx http://msdn.microsoft.com/en-us/library/ms187928.aspx

What would I recommend?

Although we now know that to Microsoft SQL Server, data type conversion only happens via CONVERT, I would still prefer to go with CAST unless I am converting dates or date-time values. The reason being CAST is an ANSI standard, CONVERT is not. CONVERT is implementation specific, and therefore may change in terms of the number of parameters or underlying processes. To the calling T-SQL statement, it is safe to assume that CAST would remain unchanged (unless the standard changes) and therefore, it would be the responsibility of Microsoft SQL Server to translate the CAST to a CONVERT implementation.

Do share your thoughts on the whole CAST v/s CONVERT myths and controversies floating around in the SQL Server world. I will be more than happy to research more and share my findings with you.

Until we meet next time,

Be courteous. Drive responsibility.

Advertisements

10 thoughts on “CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better?

  1. jcelko

    CONVERT() goes back to the old 1970’s Sybase SQL Server. We did not know how to do client/server computing so we were still formatting data for display in the database. There were also no ANSI Standards for temporal data in SQL

    Today, we should know how C/S works and know the ISO-8601 Standards used in SQL. CONVERT() is how a bad programmer gives you a warning about his code 🙂

    Like

    Reply
  2. Nakul Vachhrajani

    Thank-you, jcelko, for sharing the history of CONVERT(). I knew CONVERT was available in SQL7/2000, but did not know that CONVERT was around in the 70s :).

    Thank-you, once again!

    Like

    Reply
  3. Nakul Vachhrajani

    @SQLCommitted: True. As mentioned, CAST provides limited support for date time values (the style parameter is missing).

    Like

    Reply
  4. Paul

    The use of aliases in code is not uncommon, BETWEEN for example is executed as a pair of predicates using >= and <=

    That CAST() is a synonym of CONVERT() isn't surprising because convert was part of the product first and the standard came later. An irony for me however is that the term CONVERT is far more clear than the term CAST. For example try
    define:cast
    as a google search which currently includes "the moon cast a pale light over the cottages" and "he cast the book down on to the chair angrily" neither of which relate to changing from one thing to another.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s