Tuesday, May 24, 2011

Explicit Conversion (Convert vs Cast ) in SQL Server

For years I have been using Convert and Cast function in T-SQL with out giving much thought. Recently I was asked about the difference, I knew Cast was ANSI complaint and Convert was more specific to SQL Server beside that I was stumped. So I decided to do little digging, my first concern was to see if there are any Performance differences..
I found this article by Nakul where he tried to test performance between cast and convert.
http://beyondrelational.com/blogs/nakul/archive/2011/01/03/cast-v-s-convert.aspx
It becomes clear from the test that there is not much performance difference between the two functions but there few more things you could do with Convert which Cast cannot. Especially in a reporting scenarios where Datetime/Money types needs to be formatted, for such cases convert function proves handy. Convert also can convert string types to datetime this becomes useful when parsing through text files.
So for me I would choose convert over cast beacause of extra formatting flexibility it provides.

No comments: