Рекурсивные CTE SQL Server для безопасного обхода иерархий

Иерархия подразделений часто начинается с таблицы идентификаторов и родительских идентификаторов. Прочитать уровень просто, а для всех потомков требуется повторный обход. Рекурсивное CTE описывает его, но не доказывает, что сохранённые отношения действительно образуют правильное дерево.

Сначала определите модель. У узла только один родитель? Разрешены несколько корней и несвязанные компоненты? Внешний ключ проверяет существование родителя, но не запрещает длинный цикл. Эти правила определяют и чтение, и проверку изменений.

Разделяем начало и расширение

Пример начинает с узла 1, следует к детям и использует путь посещения вместе с пределом рекурсии.

DECLARE @Nodes table (NodeId int PRIMARY KEY, ParentId int NULL, Name nvarchar(50));
INSERT @Nodes VALUES (1,NULL,N'Company'),(2,1,N'Operations'),
(3,1,N'Engineering'),(4,2,N'Database team');

;WITH Tree AS (
    SELECT NodeId, ParentId, Name, 0 AS Depth,
           CAST('/' + CONVERT(varchar(11),NodeId) + '/' AS varchar(max)) AS Visited
    FROM @Nodes WHERE NodeId = 1
    UNION ALL
    SELECT n.NodeId, n.ParentId, n.Name, t.Depth + 1,
           CAST(t.Visited + CONVERT(varchar(11),n.NodeId) + '/' AS varchar(max))
    FROM @Nodes AS n
    JOIN Tree AS t ON n.ParentId = t.NodeId
    WHERE CHARINDEX('/' + CONVERT(varchar(11),n.NodeId) + '/', t.Visited) = 0
)
SELECT NodeId, ParentId, Name, Depth, Visited
FROM Tree
ORDER BY Visited
OPTION (MAXRECURSION 100);

Якорь возвращает Company с глубиной ноль. Рекурсивная часть находит Operations и Engineering, затем Database team. Соединение по ParentId задаёт направление; обратное условие решало бы поиск предков.

Идентификаторы пути отделены разделителями. Без них поиск 1 находил бы также 11 и 21. Если новый идентификатор уже присутствует на текущем пути, расширение исключается. Это останавливает повторение цикла, но не исправляет повреждённую связь.

Типы якоря и рекурсивного результата должны соответствовать. Оба пути явно преобразованы в varchar(max), иначе короткая исходная строка могла бы задать неподходящий тип для растущего выражения. Технический путь хранит числа, названия остаются Unicode.

ORDER BY Visited задаёт удобный порядок примера, но не универсальное правило для соседей. Текст сортируется лексически, поэтому 10 может оказаться перед 2. Если нужен бизнес-порядок, храните и стройте его явно. Порядок появления рекурсивных строк не гарантирует порядок конечной выдачи.

Ограничения являются диагностикой

MAXRECURSION 100 служит защитой, а не утверждением о допустимой глубине любой организации. При обоснованной большей глубине выберите и проверьте другой предел. Достижение ограничения должно давать видимый отказ; клиент не должен показывать частичный ответ как полную иерархию.

MAXRECURSION 0 убирает этот предел, но не доказывает отсутствие циклов. Сохраняйте защиту и проверку данных даже при легитимной потребности в глубоких структурах.

Условие пути молча прекращает повторяющуюся ветвь. Для административного отчёта отдельно обнаруживайте и сообщайте циклы вместо представления укороченного дерева как здорового. При перемещении узла проверяйте, не превратит ли предлагаемый родитель его в собственного предка.

Начало с одного корня ничего не говорит об отделённых компонентах. Для полной проверки сравнивайте достигнутые идентификаторы с ожидаемым набором. Цикл, не связанный ни с одним корнем, может полностью отсутствовать. Причина пропавших узлов тогда относится к данным, а не к скорости запроса.

Поддерживаем повторный поиск детей

На постоянной таблице индекс с первым ключом ParentId помогает находить детей. Остальные ключевые и включённые столбцы выбираются по запросам. Первичный ключ NodeId обслуживает другое направление и автоматически не ускоряет поиск всех потомков.

Измеряйте ширину ветвления и общее число потомков вместе с глубиной. Неглубокое дерево с миллионами детей может оказаться дороже длинной узкой цепочки. Посещённый путь также растёт и копируется, поэтому показанная защита не бесплатна.

Для частых огромных чтений поддеревьев рассмотрите hierarchyid, таблицу транзитивного замыкания или другое поддерживаемое представление. Они переносят сложность в запись и проверку согласованности, а не устраняют её. Изменение родителя может затронуть множество производных отношений.

Проверьте одиночный узел, соседей, длинную цепочку, несколько корней, отделённые компоненты и намеренный цикл на учебных данных. Сравните идентификаторы и ожидаемую глубину. Дополнительно проверьте несуществующий начальный узел: пустой ответ должен отличаться от успешного обхода пустой ветви. Правильный запрос объясняет как возвращённые, так и отсутствующие узлы.

Техническая документация: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.