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 301
SQL Server 300
Administration 251
DBA 240
T-SQL 231
#TSQL 229
Development 224
Tips 213
Guidance 146
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

Jan 3 2011 8:26PM 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.

However, using CAST() and CONVERT() interchangeably is one of the most capital mistakes that a developer can make. Madhivanan, a SQL Server blogger on BeyondRelational.com (http://beyondrelational.com/blogs/madhivanan/default.aspx) does a very good job at highlighting the differences between CAST() and CONVERT() in his blog post here.

To summarize, the differences that he highlights are:

  1. CAST is an ANSI standard, while CONVERT is specific to Microsoft SQL Server
  2. CAST cannot be used for formatting purposes (i.e. used to type cast something to a character string), whereas CONVERT can do so, especially for datetime and money datatypes
  3. CAST cannot convert a string to a DATETIME value of a required format (i.e. formatting as mm/dd/yyyy or dd/mm/yyyy as required etc), while the CONVERT can

Finally, he warns us to use a properly calculated size value when using the CAST and CONVERT functions to convert integer values to character data types.

All very great points, and very, very useful. Thank-you, Madhivanan for the wonderful research and enlightenment.

A Question

We can draw a general conclusion that for computation purposes, CAST should be used and for formatting of values for display on a UI or a report, CONVERT should be used. However, the interesting question is one which came up when I was following the discussion on the blog the other day –

Tuesday, July 27, 2010 10:38 AM by cute_boboi

For data extraction purpose, with 1M+ records, which method is faster/recommended ? CAST or CONVERT from:
(i) Date to varchar
(ii) Int to varchar

Today, I try to answer that question.

The Demonstration

Preparing the Environment

Let’s start by creating a table, and filling it up with some test data (Running against the AdventureWorks2008 database gives you about 356409 rows in the table):

-- Step 01. Create a test table
CREATE TABLE CASTCONVERTTest (Id INT,
                              CrDate DATETIME)

-- Step 02. Generate Test Data
INSERT INTO CASTCONVERTTest
SELECT sso1.Id, sso1.crdate
FROM sys.sysobjects sso1
CROSS JOIN sys.sysobjects sso2

Now, let’s begin by pressing Ctrl+M when in the SQL Server Management Studio or go to Query->Include Actual Execution Plan to enable showing the Actual Execution Plan.

Conversion of INT to VARCHAR

In order to see the performance of using CAST and CONVERT on INT to VARCHAR conversion, we will use the following script. At each stage, we will be using DBCC freeproccache to remove all elements from the plan cache.

/***********************************************************
Press Ctrl+M (or go to Query->Include Actual Execution Plan)
************************************************************/
-- Step 03. Test conversion of INT to VARCHAR
-- A. Remove all elements from the plan cache
DBCC freeproccache
GO

-- B. Check the performance of CAST
SELECT CAST(Id AS VARCHAR(11))
FROM CASTCONVERTTest
GO

-- C. Remove all elements from the plan cache
DBCC freeproccache
GO

-- D. Check the performance of CONVERT
SELECT CONVERT(VARCHAR(11),Id)
FROM CASTCONVERTTest
GO

Let’s look at the Actual Execution Plan used by SQL Server. As you can see, we get a 50-50 split, which means that both CAST and CONVERT perform equally well for the INT to VARCHAR conversion.

image

Conversion of DATETIME to VARCHAR

In order to see the performance of using CAST and CONVERT on DATETIME to VARCHAR conversion, we will use the following script. At each stage, we will be using DBCC freeproccache to remove all elements from the plan cache.

-- Step 04. Test conversion of DATETIME to VARCHAR
-- A. Remove all elements from the plan cache
DBCC freeproccache
GO

-- B. Check the performance of CAST
SELECT CAST(CrDate AS VARCHAR(25))
FROM CASTCONVERTTest
GO

-- C. Remove all elements from the plan cache
DBCC freeproccache
GO

-- D. Check the performance of CONVERT
SELECT CONVERT(VARCHAR(25),CrDate,106)
FROM CASTCONVERTTest
GO

Let’s look at the Actual Execution Plan used by SQL Server. As you can see, again we get a 50-50 split, which means that both CAST and CONVERT perform equally well for the DATETIME to VARCHAR conversion.

image

The Cleanup

Finally, as always, let’s cleanup the environment.

-- Step 05. Cleanup!
DROP TABLE CASTCONVERTTest

In Conclusion

In conclusion, we can safely conclude that both CAST and CONVERT perform equally well for the following conversions:

  1. INT to VARCHAR
  2. DATETIME to VARCHAR

I hope the above is a satisfactory reply to the question we started with.

Powered with the research conducted by Madhivanan and with the afore described performance test, the choice is now up to the reader. Do share your practices with CAST & CONVERT on this blog. Also, do mention the reasons why you practice a particular rule of thumb, if possible.

Be courteous. Drive responsibly.

Tags: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, DBA, Myths,


Nakul Vachhrajani
4 · 36% · 11543
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"CAST v/s CONVERT" rated 5 out of 5 by 2 readers
CAST v/s CONVERT , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]