Как реализовать высокопроизводительное древовидное представление в SQL Server 2005

Используйте CTE.

Учитывая древовидную структуру таблицы:

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

, этот запрос вернет id, parent и уровень глубины, упорядоченные в виде дерева:

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

Замените parent = 0 на parent = @parent, чтобы получить только ветвь дерева.

При наличии индекса table (parent) этот запрос будет эффективно работать с очень большой таблицей, поскольку он будет рекурсивно использовать INDEX LOOKUP для поиска всех дочерних элементов для каждого родителя.

Чтобы обновить определенную ветку, введите:

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
        )

где @parent — корень ветки.

Сначала вы должны задать себе следующие вопросы: 1) Каково соотношение модификаций и чтений? (= в основном статическое дерево или постоянно меняющееся?) 2) Как глубоко и насколько вы ожидаете, что дерево будет расти?

Вложенные наборы отлично подходят для в основном статических деревьев, где вам нужны операции над целыми ветвями. Без проблем справляется с глубокими деревьями.

Материализованный путь хорошо работает для динамических (изменяющихся) деревьев с ограниченной/предсказуемой глубиной.

Рекурсивные CTE идеально подходят для очень маленьких деревьев, но операции ветвления ("получить все дочерние элементы в этой ветке...") становятся очень дорогостоящими с глубоким/большим деревом.

Ознакомьтесь с книгой Джо Селко о деревьях и иерархиях. несколько способов решения проблемы иерархии. Выбранная вами модель будет зависеть от того, как вы взвешиваете запросы, обновления и сложность. Вы можете сделать поиск довольно быстрым (особенно для получения всех дочерних элементов в узле), используя модель списка смежности, но обновления дерева выполняются медленнее.

Если у вас много обновлений и выборок, лучшим вариантом будет Модель перечисления путей, которая кратко описана здесь:

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Я удивлен, что никто не упомянул о таблице закрытия. Очень эффективен для чтения и довольно прост в написании.