Hierarchical queries (or recursive queries) in SQL Server 2005/2008

The question is ... how do you run hierarchical queries in SQL server ? I was trying to find something similar to CONNECT BY..PRIOR clause in Oracle. We stepped in to CTE (Common Table Expression). Awesome feature !

* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)

WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
    SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr    
    WHERE mgr_id is null


    UNION ALL
-- Recursive member definition
    SELECT e.mgr_id, e.emp_id,Level + 1
    FROM test_emp_mgr AS e
    
    INNER JOIN DirectReports AS d
        ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;