Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Jul 18 2011 12:00AM by Nakul Vachhrajani   

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: http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx.

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.

Tags: 


Nakul Vachhrajani
4 · 36% · 11645
23
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

9  Comments  

  • Nakul, very well documented post.. Thanks for sharing.

    commented on Jul 3 2011 11:16PM
    Hardik Doshi
    20 · 9% · 2864
  • Nice mate!

    commented on Jul 18 2011 1:41AM
    krishnavamshi24
    534 · 0% · 70
  • 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 :)

    commented on Jun 5 2012 9:37AM
    jcelko
    452 · 0% · 87
  • 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!

    commented on Jun 5 2012 9:46AM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Well, In CAST you can't apply Style where as in Convert you can apply style.

    SQLCommitted

    commented on Jun 7 2012 1:30AM
    sqlcommitted
    1081 · 0% · 24
  • @SQLCommitted: True. As mentioned, CAST provides limited support for date time values (the style parameter is missing).

    commented on Jun 7 2012 1:36PM
    Nakul Vachhrajani
    4 · 36% · 11645
  • Very nice post, thanks for sharing!

    commented on Oct 9 2013 1:22PM
    robertosarquis
    2772 · 0% · 4

Your Comment


Sign Up or Login to post a comment.

"CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better?" rated 5 out of 5 by 23 readers
CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better? , 5.0 out of 5 based on 23 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]