Using a CTE for Heirarchical Query

A project I did a few years back was for a large company with thousands of employees.  The project was essentially building a system to allow managers to evaluate employees and give them quarterly bonuses.  We had to take raw data out of a PeopleSoft repository and build the company heirarchy so the employees showed up on the appropriate managers screen, as well as so the bonuses for employees at the lower levels rolled up to the higher managers level so they could manage their department budgets appropriately.  The biggest pain in this process was building queries to create this hierarchical structure...And man, was it slow!  We eventually did some performance tuning on it and got it to be acceptable, but it was still quite a query.  Many lines of code!

I wish I had that same project on my new project list for later this year!  Yukon is introducing the concept of "Common Table Expression", or CTE.  It essentially lets you perform a recursive query...perfect for a hierarchy!

TheServerSide.NET has an article on this exact subject.

One of these days, I'd be interested in (finally) installing a copy of Yukon and running a CTE and compare the performance with one of the "old" queries we used in that project.  My guess is that it would be a big improvement.