title: Hierarchical Queries in SQL Server 2005
body: <p>Way back when I was working in an Oracle shop I took the CONNECT_BY for granted.  Now I'm stuck working with SQL Server 2005 and have some nasty object hierarchies.  Specifically, we have a self referencing table where all child records have a column with their parent's id.  Currently we have a view that maps children to levels in the hierarchy and a nasty query that does the heavy lifting to connect parents with their children.  While this method works, it is far from elegant and reeks of taint.  I'm just curious how other people retrieve hierarchical data from SQL Server 2005.</p>