Rekursive CTEs in SQL Server sicher für Hierarchien nutzen

Eine Organisationsstruktur beginnt häufig als Tabelle mit Kennung und Elternkennung. Eine Ebene ist einfach zu lesen; alle Nachfahren erfordern wiederholte Schritte. Eine rekursive CTE beschreibt diese Schritte, beweist aber nicht, dass die gespeicherten Beziehungen tatsächlich einen gültigen Baum bilden.

Klären Sie das Modell. Hat jeder Knoten genau einen Elternknoten? Sind mehrere Wurzeln oder getrennte Bereiche erlaubt? Ein Fremdschlüssel prüft die Existenz des Elternknotens, verhindert aber keinen längeren Kreis. Davon hängen Abfrage und Schreibprüfungen gemeinsam ab.

Startmenge und Erweiterung unterscheiden

Das Beispiel beginnt bei Knoten 1, folgt Kindern und verwendet einen besuchten Pfad sowie ein Tiefenlimit.

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

Der Anker liefert Company auf Ebene null. Der rekursive Teil findet Operations und Engineering und danach Database team. Die Verknüpfung über ParentId bestimmt die Richtung. Eine umgekehrte Verknüpfung würde Vorfahren statt Nachfahren suchen.

Der Pfad enthält abgegrenzte Kennungen. Ohne Trennzeichen würde die Suche nach 1 auch 11 oder 21 finden. Befindet sich die neue Kennung bereits im Pfad dieses Zweigs, wird die Erweiterung ausgeschlossen. Das verhindert endlose Wiederholung, repariert aber keine fehlerhafte Beziehung.

Anker und rekursiver Teil brauchen passende Datentypen. Beide Pfadausdrücke werden ausdrücklich nach varchar(max) konvertiert. Ein kurzer Ankertext könnte sonst einen ungeeigneten Typ für den später wachsenden Ausdruck festlegen. Der technische Pfad enthält Zahlen, während Anzeigenamen Unicode bleiben.

ORDER BY Visited dient nur einer einfachen Beispielsortierung. Textpfade sortieren lexikalisch; 10 kann deshalb vor 2 stehen. Eine fachliche Reihenfolge der Geschwister muss eigens gespeichert und abgebildet werden. Die Erzeugungsreihenfolge einer Rekursion garantiert keine Ausgabereihenfolge.

Grenzen und Ausschlüsse ernst nehmen

MAXRECURSION 100 schützt den Versuch, behauptet aber keine allgemeingültige Organisationsgröße. Wenn das Modell größere Tiefen erlaubt, wählen und prüfen Sie eine begründete Grenze. Das Erreichen der Grenze muss sichtbar fehlschlagen; ein Client darf ein unvollständiges Resultat nicht als vollständigen Baum behandeln.

MAXRECURSION 0 entfernt diese Begrenzung, beweist aber keine Kreisfreiheit. Zyklenschutz und Datenprüfung bleiben erforderlich, auch wenn legitime Strukturen eine höhere Grenze verlangen.

Das Pfadprädikat beendet wiederholte Zweige still. Für Verwaltungsberichte sollten Kreise zusätzlich erkannt und gemeldet werden, statt den verkürzten Baum als gesund darzustellen. Beim Verschieben eines Knotens muss geprüft werden, ob der neue Elternknoten den verschobenen Knoten zu seinem eigenen Vorfahren machen würde.

Eine Abfrage ab einer Wurzel beschreibt außerdem keine getrennten Komponenten. Vergleichen Sie bei einer Gesamtprüfung die erreichbaren Kennungen mit dem vorgesehenen Bestand. Ein Kreis ohne Verbindung zu einer Wurzel kann vollständig fehlen. Fehlende Knoten sind dann kein Leistungsproblem, sondern ein Datenbefund.

Wiederholte Kindersuche unterstützen

Ein Index mit ParentId als erster Spalte kann auf einer permanenten Tabelle das Finden der Kinder unterstützen. Weitere Schlüssel- oder eingeschlossene Spalten richten sich nach der tatsächlichen Abfrage. Der Primärschlüssel auf NodeId bedient eine andere Suchrichtung.

Messen Sie Verzweigungsbreite und Gesamtmenge neben der Tiefe. Ein flacher Baum mit Millionen Kindern kann teurer sein als eine lange schmale Kette. Auch der besuchte Pfad wächst und wird bei weiteren Schritten kopiert. Das anschauliche Muster ist daher nicht kostenlos.

Bei sehr häufigen großen Teilbaumabfragen können hierarchyid, eine Closure-Tabelle oder andere gepflegte Darstellungen passen. Diese verlagern Aufwand in Änderungen und Konsistenzregeln. Prüfen Sie deshalb den gesamten Lese-Schreib-Mix und nicht nur einen schnellen Bericht.

Testen Sie Einzelknoten, Geschwister, tiefe Ketten, mehrere Wurzeln, getrennte Bereiche und einen absichtlichen Kreis in Übungsdaten. Vergleichen Sie Kennungen und erwartete Tiefe. Eine verlässliche Abfrage erklärt ebenso, welche gespeicherten Knoten aus welchem Grund fehlen.

Technische Referenzen: Microsoft Learn: Recursive queries · Microsoft Learn: MAXRECURSION.