Jeff Moden (5/17/2012)
Sorry... the way my comment relates to the articles you cited and to Dwaine's article is that, while recursion is a mathematically sound way of expressing a problem, none of them explain that the use of recursive CTEs is usually not a good way to solve the problem either performance wise or resource usage wise simply because of the way SQL Server does the recursion. I'd like to see folks start to take a little responsibility in that area by doing a resource usage and performance comparison between the rCTE's and the equivalent While Loops.
It still seems that your issues are more with the author (and Craig Freedman) rather than me. I'm not sure if a more efficient WHILE-based solution is possible here; I was simply responding to the request for more details about how recursive CTEs actually work. Perhaps Dwain will respond to explain why a comparison with WHILE was not done.
For what it's worth, I have never had to produce the sort of result shown in the article, but if I did, I might try to find a bit-position T-SQL solution, or perhaps write a solution in a language more suited to high-performance iteration (one of the CLR ones).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi