Hierarchical Queries in SQL Server 2005

Question

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.

Answer 1

in SQL Server 2005 you can use Common Table Expressions (CTE) for this.

Answer 2

This creates your typical hierarchical table and uses a CTE to select the hierarchy structure and create a path for each item.

CREATE TABLE tblHierarchy (ID int, ParentID int NULL, Name varchar(128));

INSERT INTO tblHierarchy VALUES (1, NULL, '1');
INSERT INTO tblHierarchy VALUES (2, NULL, '2');
INSERT INTO tblHierarchy VALUES (3, NULL, '3');
INSERT INTO tblHierarchy VALUES (4, 1, '1.1');
INSERT INTO tblHierarchy VALUES (5, 1, '1.2');
INSERT INTO tblHierarchy VALUES (6, 4, '1.1.1');

WITH Parent AS
(
    SELECT
     ID,
     ParentID,
     Name AS Path
    FROM
     tblHierarchy
    WHERE
     ParentID IS NULL

    UNION ALL

    SELECT
     TH.ID,
     TH.ParentID,
     CONVERT(varchar(128), Parent.Path + '/' + TH.Name) AS Path
    FROM
     tblHierarchy TH
    INNER JOIN
     Parent
    ON
     Parent.ID = TH.ParentID
)
SELECT * FROM Parent

OUTPUT:

ID  ParentID Path
1   NULL  1
2   NULL  2
3   NULL  3
4   1  1/1.1
5   1  1/1.2
6   4  1/1.1/1.1.1

Answer 3

Just FYI. SQL Server 2008 supports a new data type Hierarchy ID.

Answer 4

Read this:

http://www.sitepoint.com/article/hierarchical-data-database/2/

It should give you some ideas...

Answer 5

Having used both, I found CONNECT BY is somewhat more flexible and easier to use than CTE's. The question is not dissimilar to one I answered a few weeks ago. See Here for a brief comparison of CONNECT BY and CTE's and Here for an example of a query using CTE's.