GPO (6/17/2013)
Interesting problem. Thanks for posting. I must confess I have not yet read the whole article, but from what I've seen (replacing a cursor or a while loop with a recursive CTE), I have a number of questions:1) Wouldn't it be possible to do the same thing using a tally table? See http://www.sqlservercentral.com/articles/T-SQL/62867/
2) What metrics are you using to determine which method is faster?
As far as I'm aware, recursive CTEs are RBAR just like cursors and while loops.
Cheers
GPO
(now I'll go back and read it properly to see whether I've just made a goose out of myself!)
I will have to agree with GPO here
CTE's may or may not be better in terms of performance compared to CURSORS
The problem you have taken can be solved using a Tally table as well
Please go through the article below which compares the performance of CTE's and Tally table in such situations
http://www.sqlservercentral.com/articles/T-SQL/74118/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/