I had the same thought when I upgraded to SQL Server 2005.

I have a TVF to return just the "key" data in my hierarchical tree structure. It uses the stack code as published by Microsoft.

I experimented using a CTE and found that I was getting much better performance from my TVF. In addition, the lack of a sorting capability for the intermediate nodes (which my TVF does) killed any use of a CTE. Oh how I really could use Oracle's CONNECT BY... in SQL Server!

See more details in my earlier post:

http://www.sqlservercentral.com/Forums/Topic366130-8-1.aspx#bm366402