Parcourir une hiérarchie SQL Server avec une CTE récursive

Une organisation ou un catalogue commence souvent par une table contenant identifiant et parent. Lire un niveau est simple; trouver tous les descendants demande des étapes répétées. Une CTE récursive les exprime clairement sans garantir que les relations stockées forment un arbre valide.

Définissez d'abord le modèle. Chaque noeud possède-t-il un seul parent? Plusieurs racines ou composants séparés sont-ils autorisés? Une clé étrangère impose l'existence du parent, mais n'empêche pas un cycle plus long. Ces décisions déterminent lecture et validation des modifications.

Séparer départ et expansion

L'exemple part du noeud 1 et suit ses enfants, avec un chemin visité et une limite de récursion.

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);

L'ancre produit Company à profondeur zéro. La partie récursive trouve Operations et Engineering, puis Database team. La jointure sur ParentId définit la direction; l'inverser répondrait à une recherche d'ancêtres.

Le chemin contient des identifiants délimités. Sans délimiteurs, rechercher 1 trouverait aussi 11 ou 21. Une ligne dont l'identifiant figure déjà sur le chemin courant est exclue. Cela arrête une répétition cyclique sans réparer la relation incorrecte.

Les types de l'ancre et de la partie récursive doivent correspondre. Les deux chemins sont explicitement varchar(max), évitant qu'une chaîne initiale courte impose un type incompatible avec la croissance. Le chemin utilise des identifiants numériques; les noms restent Unicode.

ORDER BY Visited fournit un ordre pratique de démonstration, pas un ordre métier universel. Le tri textuel peut placer 10 avant 2. Stockez et construisez explicitement l'ordre des frères si nécessaire. La production récursive ne garantit pas la présentation finale.

Interpréter limites et exclusions

MAXRECURSION 100 est une protection, pas une loi sur la profondeur de toute organisation. Pour une profondeur légitime supérieure, choisissez une limite justifiée et testez-la. Son dépassement doit échouer visiblement; un client ne doit pas accepter un résultat partiel comme une hiérarchie complète.

MAXRECURSION 0 retire cette protection sans prouver l'absence de cycles. Maintenez les contrôles de données même lorsque le modèle demande davantage de niveaux.

Le prédicat du chemin arrête silencieusement une branche répétée. Un rapport administratif doit donc détecter et signaler les cycles séparément. Lors d'un déplacement, vérifiez si le parent proposé transformerait le noeud déplacé en son propre ancêtre.

Partir d'une racine ne dit rien des composants déconnectés. Comparez les identifiants atteints à la population attendue lors d'un audit complet. Un cycle sans liaison à une racine peut manquer entièrement dans le rapport. Des lignes absentes peuvent révéler une anomalie de données, pas un problème de performance.

Adapter l'accès aux expansions répétées

Sur une table permanente, un index commençant par ParentId peut faciliter la recherche des enfants. Choisissez les autres colonnes selon les besoins réels. La clé primaire sur NodeId sert à une autre direction et ne rend pas automatiquement efficace la recherche de descendants.

Mesurez largeur des branches et nombre total de descendants, pas seulement profondeur. Un arbre peu profond avec des millions d'enfants peut coûter davantage qu'une chaîne longue et étroite. Le chemin visité grandit aussi et se copie à chaque étape: cette protection illustrative n'est pas gratuite.

Pour des lectures massives de sous-arbres très fréquentes, examinez hierarchyid, une table de fermeture ou une autre représentation maintenue. Ces choix déplacent la complexité vers les mises à jour et les contrôles de cohérence. Mesurez le compromis complet.

Testez noeud unique, frères, chaîne profonde, plusieurs racines, composants séparés et cycle volontaire sur des données jetables. Comparez identifiants et profondeur attendue. Une bonne requête explique ce qu'elle retourne, mais aussi les raisons pour lesquelles certaines lignes restent hors du résultat.

Références techniques: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.