CAST vs CONVERT or another solution?

Question

CAST vs CONVERT or another solution?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

S_Kumar_S (3/28/2012)
I too executed your query several times on a development server. Depending on load, either of them worked faster but the tests never convinced me that one is faster than other....
May be a stand alone machine might show some standard result but I trust still the performance difference will be negligible...
Antares686 (3/28/2012)
SQLKnowItAll (3/28/2012)
S_Kumar_S (3/28/2012)
Hi
Can you pls guide to some link that shows this difference. Most of the places I know just say that the performance is similar. e.g this one:
http://www.informit.com/articles/article.aspx?p=31283&seqNum=4
SQLKnowItAll (3/27/2012)
S_Kumar_S (3/27/2012)
Using cast or convert won't make any differencr performance wise.
That's not true...
Why do you need a link when you can test? This is just one way to test and look at different metrics.
OK, I have to agree with Kumar either one can be used without a performance hit. The stats are always the same for either one. They produce the same reads/writes and generate the same execution plan. There is no difference between the two with performance. The same arguement has been made about IsNull and Coalesce. Other than parameter options they have the same performance.

Hmm... After some more research, I agree as well. I was remembering a date conversion I was doing comparing different ways to 0 out the time of a datetime and I believe that it was some other nested converts that drastically changed performance. In my recent research, I found a great little piece here that shows how a CAST is actually interpreted on the back end as a CONVERT. Thanks for making me search to clarify my own incorrect thoughts on this!

Jared
CE - Microsoft

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

It was really surprising to know that cast is internally implemeted as Convert!!!!

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Jared SSC Guru Points: 61793 More actions · Answer 3

S_Kumar_S (3/28/2012)
It was really surprising to know that cast is internally implemeted as Convert!!!!

I would say knowing that performance is the same, it should have been assumed. If they operated differently on the back end, it would be impossible to say that performance is ALWAYS the same. Thanks again for making me work to find answers :hehe:

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

SQLKnowItAll (3/28/2012)
S_Kumar_S (3/28/2012)
I too executed your query several times on a development server. Depending on load, either of them worked faster but the tests never convinced me that one is faster than other....
May be a stand alone machine might show some standard result but I trust still the performance difference will be negligible...
Antares686 (3/28/2012)
SQLKnowItAll (3/28/2012)
S_Kumar_S (3/28/2012)
Hi
Can you pls guide to some link that shows this difference. Most of the places I know just say that the performance is similar. e.g this one:
http://www.informit.com/articles/article.aspx?p=31283&seqNum=4
SQLKnowItAll (3/27/2012)
S_Kumar_S (3/27/2012)
Using cast or convert won't make any differencr performance wise.
That's not true...
Why do you need a link when you can test? This is just one way to test and look at different metrics.
OK, I have to agree with Kumar either one can be used without a performance hit. The stats are always the same for either one. They produce the same reads/writes and generate the same execution plan. There is no difference between the two with performance. The same arguement has been made about IsNull and Coalesce. Other than parameter options they have the same performance.
Hmm... After some more research, I agree as well. I was remembering a date conversion I was doing comparing different ways to 0 out the time of a datetime and I believe that it was some other nested converts that drastically changed performance. In my recent research, I found a great little piece here that shows how a CAST is actually interpreted on the back end as a CONVERT. Thanks for making me search to clarify my own incorrect thoughts on this!

It isn't the CONVERT that was the performance problem. More likely, it was a conversion to VARCHAR that was the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions