If the following snippet catches your attention, you ought to check The Last Component. It's real, and they don't touch the compiler, they do it overloading operators. Brilliant
TheServerSide.Net has a nice article on performing Hierarchical queries using CTEs on SQL Server 2005. http://www.theserverside.ne