More efficient method for grabbing all child units

Question

I have a table in SQL that links to itself through parentID. I want to find the children and their children and so forth until I find all the child objects. I have a recursive function that does this but it seems very ineffective.

Is there a way to get sql to find all child objects? If so how?

Using: Microsoft SQL Server Management Studio Express 9.00.2047.00

Answer 1

You are looking for CTEs.
Using Common Table Expressions, MSDN.
You need at least SQL Server 2005.

Answer 2

Have a look at using Sql Server 2005 CTEs.

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(10),
        ParentID INT
)

INSERT INTO @Table SELECT 1, 'A', NULL
INSERT INTO @Table SELECT 2, 'B', NULL
INSERT INTO @Table SELECT 3, 'C', 1
INSERT INTO @Table SELECT 4, 'D', 1
INSERT INTO @Table SELECT 5, 'E', 4
INSERT INTO @Table SELECT 5, 'F', 2

;WITh Parents AS (
    SELECT  *,
            CAST(Val + '/'  AS VARCHAR(100))PathVal
    FROm    @Table
    WHERE   ParentID IS NULL
    UNION ALL
    SELECT  t.*,
            CAST(p.PathVal + t.Val + '/' AS VARCHAR(100))
    FROM    @Table t INNER JOIN 
            Parents p ON t.ParentID = p.ID
)
SELECT  *
FROM    Parents

Depending on the depth of the tree, you might want to take a look at

MAXRECURSION query hint

Answer 3

I would suggest something like the Nested Set Model.

The idea is to store an additional two integers (usually called "left" and "right") for each node, calculated according to a system you can read more about by following the link. Then queries for all descendants of an arbitrary node becomes trivial.

Edit: Here is a more detailed description.