How to implement high performance tree view in SQL Server 2005

Question

What is the best way to build the table that will represent the tree? I want to implement a select ,insert ,update and delete that will work well with big data. The select for example will have to support "Expand ALL" - getting all the children (and there children) for a given node.

Answer 1

Use CTE's.

Given the tree-like table structure:

id parent name
1  0      Electronics
2  1      TV
3  1      Hi-Fi
4  2      LCD
5  2      Plasma
6  3      Amplifiers
7  3      Speakers

, this query will return id, parent and depth level, ordered as a tree:

WITH    v (id, parent, level) AS
        (
        SELECT  id, parent, 1
        FROM    table
        WHERE   parent = 0
        UNION ALL
        SELECT  id, parent, v.level + 1
        FROM    v
        JOIN    table t
        ON      t.parent = v.id
        )
SELECT  *
FROM    v

id parent name
1  0      Electronics
2  1        TV
4  2          LCD
5  2          Plasma
3  1        Hi-Fi
6  3          Amplifiers
7  3          Speakers

Replace parent = 0 with parent = @parent to get only a branch of a tree.

Provided there's an index on table (parent), this query will efficiently work on a very large table, since it will recursively use INDEX LOOKUP to find all chilrden for each parent.

To update a certain branch, issue:

WITH    v (id, parent, level) AS
        (
        SELECT  id, parent, 1
        FROM    table
        WHERE   parent = 0
        UNION ALL
        SELECT  id, parent, v.level + 1
        FROM    v
        JOIN    table t
        ON      t.parent = v.id
        )
UPDATE  table t
SET     column = newvalue
WHERE   t.id IN
        (
        SELECT  id
        FROM    v
        )

where @parent is the root of the branch.