IMO, SQL Server does not handle trees well. The reason is that tree traversal is naturally row-oriented and SQL is based on set theory. There are some known solution. You could do a traversal recursively and insert the nodes into a temporary table. Joe Celko published a solution that I like in his book SQL for Smarties. Also, Ben-Gan published an article in SQL Server Magazine.

I have implemented Joe Celko's solution with great success. The only negative is that it requires a pre-order traversal, which is not a natural SQL operation.

You can go the recursive route as long as the longest path is less than 33 levels. SQL Server only allows nested procedure calls to 32 levels.

-Mike