Steven James Gray (6/4/2010)
...I've yet to see any demonstration that the CTE itself is unsuitable for synthesising the data on-demand - it's not the fastest mechanism (and I've never claimed it would be), but it's also not an order of magnitute worse. Like procedural texture generation in 3D modelling systems, it trades some performance for the ability to switch to arbitrary levels of granularity by modifications to a single area.
...
Here is the problem, you are using an inefficient method of generating the data. Another developer is going to come around, see this code and say "Great, just what I need for what I am working on!" Problem, turns out this developer is trying to use it on multi-million row data sets and can't figure out why it doesn't work well.
Also, what happens if you start using this same code on much larger datasets and start seeing performace issues?
Some very simple testing showed the inefficiences in the recursive CTE, including additional cpu time and IO requirements of the method.