I have been doing hierarchical queries in RDBMS (Oracle, SQL Server 2000/2005, DB2) for 20+ years.

For SQL Server, there are a number of ways to do this in T-SQL.  However, the solution can vary depending upon your application and the use patterns.

Generally, the best way that I have done this is to write a UDF that returns a table result rather than in-line SQL code.

Solutions are:

1. A block of code that navigates down the tree using "push" and "pop" logic.  See Microsoft KB article 248915.

Benefits:

1. Full control over what you're doing

2. Ordering of the intermediate values, if necessary.

2. If you're using SQL Server 2005, you can use the recursive CTE (Common Table Expression) logic.  There are numerous examples of this.

Drawbacks:

1. Unfortunately, the SQL Server 2005 recursive Common Table Expression (CTE) does not return the rows in hierarchical tree order.  Contrast with Oracle's CONNECT BY syntax.  In addition, there is no way to order (sort) the intermediate nodes like Oracle's CONNECT BY ... ORDER SIBLINGS BY syntax.

Therefore, a "sort field" (column) must be constructed by the CTE portion of the query which can be used later.

2. Performace.  My benchmarking testing (SQL Server 2005 SP1) using a CTE vs. a "tree walk" function showed that the "tree walk" function was much faster than the CTE.  Your mileage (performance) may vary.