I used to (obviously, about 4 years back) use the BOL example because, like a lot of folks, you don't know what you don't know. 😛

Looking back through this thread, you don't need to provide any test data. The example that JHood wrote above (which includes a modicum of test data) on this very same thread is the very same method I was going to suggest that you replace the BOL example with. It's a much more set-based method (Celko calls it "Lasagne" code because of its "layering" effect) and is a direct replacement for the CTE method. After all, all the recursive CTE does is load the root nodes and then load each level in a loop. The loops are simply a control mechanism to control which set of nodes are loaded for each level.

JHood also creates a "hierarchy path" in his code which he uses for sorting the data in "hierarchical order". As he says in his post, it's 9 times more efficient than the BOL example.