Thanks to all the recent responders. This article was first published a few years back and its main value I think is that it explores some non-traditional cases.
I have since sort of lost my fascination for rCTEs, but they were very interesting as an advanced topic when I was first starting out.
Early in the article I threatened to retch if anyone brought up the same tired old hierarchy traversal as a rCTE solution. Who would have thought that I'd write something about that eventually, albeit with my own take on it.
https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/
That article is good because it also shows a comparison vs. a loop and what Halloween protection can do to you.
Thanks also to the RBAR police for looking in and keeping people honest.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St