Hierarchische Daten in SQL Server – Rekursive CTEs, HierarchyID & Co.

1. Einleitung – Was sind hierarchische Daten?

Organisationsstrukturen, Kategorien, Stücklisten, Kommentarthreads – überall begegnen uns Baumelemente, bei denen jeder Eintrag einen oder mehrere untergeordnete Einträge haben kann. SQL Server bietet mehrere Ansätze, solche Daten zu speichern und zu durchsuchen: die klassische Adjacency List (ParentID), den nativen Datentyp HierarchyID sowie die rekursive Common Table Expression (CTE), die beide Modelle abfragbar macht. In diesem Artikel zeige ich an konkreten Beispielen mit Ausgaben, wie Sie hierarchische Daten effizient verarbeiten.

2. Adjacency List – Die klassische Parent-Child-Tabelle

Das einfachste Modell: Jede Zeile enthält eine ID und eine Referenz auf die übergeordnete Zeile (NULL für Wurzelknoten). Wir erstellen eine einfache Abteilungshierarchie.

CREATE TABLE dbo.Department (
  DeptID INT PRIMARY KEY,
  Name VARCHAR(100),
  ParentID INT NULL,
  CONSTRAINT FK_Department_Parent FOREIGN KEY (ParentID) REFERENCES dbo.Department(DeptID)
);

INSERT INTO dbo.Department VALUES
(1, 'Vorstand', NULL),
(2, 'IT', 1),
(3, 'Entwicklung', 2),
(4, 'Betrieb', 2),
(5, 'Finanzen', 1),
(6, 'Buchhaltung', 5),
(7, 'Controlling', 5);

Rekursive CTE – Den ganzen Baum ausgeben

WITH DeptCTE AS (
  -- Anker: Wurzelknoten
  SELECT DeptID, Name, ParentID,
      0 AS Level,
      CAST(Name AS VARCHAR(500)) AS Path,
      CAST('' AS VARCHAR(500)) AS Indent
  FROM dbo.Department WHERE ParentID IS NULL

  UNION ALL

  -- Rekursion: Unterknoten
  SELECT d.DeptID, d.Name, d.ParentID,
      p.Level + 1,
      CAST(p.Path + ' → ' + d.Name AS VARCHAR(500)),
      CAST(REPLICATE(' ', p.Level+1) AS VARCHAR(500))
  FROM dbo.Department d
  INNER JOIN DeptCTE p ON d.ParentID = p.DeptID
)
SELECT Level, Indent + Name AS Hierarchie, Path
FROM DeptCTE
ORDER BY Path;

Ausgabe:

3. HierarchyID – Nativer Datentyp für Bäume

Seit SQL Server 2008 gibt es den Datentyp hierarchyid. Er speichert die Position eines Knotens im Baum kompakt und ermöglicht blitzschnelle Abfragen von Teilbäumen, ohne rekursive CTEs. Wir erstellen dieselbe Abteilungshierarchie mit hierarchyid.

CREATE TABLE dbo.DepartmentH (
  DeptID INT PRIMARY KEY,
  Name VARCHAR(100),
  Node HIERARCHYID NOT NULL
);

-- Knotenwerte manuell festlegen (oder mit GetReparentedValue berechnen)
INSERT INTO dbo.DepartmentH VALUES
(1, 'Vorstand', hierarchyid::Parse('/')),
(2, 'IT', hierarchyid::Parse('/1/')),
(3, 'Entwicklung', hierarchyid::Parse('/1/1/')),
(4, 'Betrieb', hierarchyid::Parse('/1/2/')),
(5, 'Finanzen', hierarchyid::Parse('/2/')),
(6, 'Buchhaltung', hierarchyid::Parse('/2/1/')),
(7, 'Controlling', hierarchyid::Parse('/2/2/'));

-- Alle Nachfahren des Knotens /1/ (IT) abrufen
DECLARE @node HIERARCHYID = hierarchyid::Parse('/1/');
SELECT DeptID, Name, Node.ToString() AS NodePath,
    Node.GetLevel() AS Level
FROM dbo.DepartmentH
WHERE Node.IsDescendantOf(@node) = 1;

Ausgabe:

4. Nützliche Funktionen für rekursive Abfragen

Mit der Adjacency List lassen sich komplexe Szenarien realisieren: Unterbaum löschen, Pfad von der Wurzel berechnen, maximale Tiefe ermitteln. Hier einige praktische Beispiele.

Gesamten Unterbaum eines Knotens löschen

-- Löscht IT (ID 2) und alle Unterabteilungen (Entwicklung, Betrieb)
WITH Subtree AS (
  SELECT DeptID FROM dbo.Department WHERE DeptID = 2
  UNION ALL
  SELECT d.DeptID FROM dbo.Department d
  INNER JOIN Subtree s ON d.ParentID = s.DeptID
)
DELETE FROM dbo.Department WHERE DeptID IN (SELECT DeptID FROM Subtree);

Pfad von einem Blatt zur Wurzel

WITH PathCTE AS (
  SELECT DeptID, Name, ParentID, CAST(Name AS VARCHAR(MAX)) AS PathUp
  FROM dbo.Department WHERE DeptID = 4 -- 'Betrieb'
  UNION ALL
  SELECT d.DeptID, d.Name, d.ParentID,
      CAST(d.Name + ' ← ' + p.PathUp AS VARCHAR(MAX))
  FROM dbo.Department d
  INNER JOIN PathCTE p ON d.DeptID = p.ParentID
)
SELECT TOP 1 PathUp FROM PathCTE WHERE ParentID IS NULL;

Ausgabe: Vorstand ← IT ← Betrieb

5. Materialized Path – Eine Alternative zwischen Adjacency List und HierarchyID

Sie speichern den gesamten Pfad als String (z. B. '/1/2/5/'). Abfragen auf Unterbäume werden zu LIKE-Präfix-Abfragen. Das ist schnell, da ein Standardindex genutzt werden kann, aber die Pfadlänge ist begrenzt und die Aktualisierung (z. B. Verschieben eines Teilbaums) ist aufwendig.

CREATE TABLE dbo.DepartmentMP (
  DeptID INT PRIMARY KEY,
  Name VARCHAR(100),
  Path VARCHAR(200) NOT NULL
);
INSERT INTO dbo.DepartmentMP VALUES
(1, 'Vorstand', '/1/'),
(2, 'IT', '/1/2/'),
(3, 'Entwicklung', '/1/2/3/'),
(4, 'Betrieb', '/1/2/4/'),
(5, 'Finanzen', '/1/5/'),
(6, 'Buchhaltung', '/1/5/6/'),
(7, 'Controlling', '/1/5/7/');

CREATE INDEX IX_DepartmentMP_Path ON dbo.DepartmentMP (Path);

-- Alle Kinder von IT (Path beginnt mit /1/2/)
SELECT Name, Path
FROM dbo.DepartmentMP
WHERE Path LIKE '/1/2/%' AND Path <> '/1/2/';

6. Vergleiche der Methoden (Wann verwende ich was?)

7. Performance-Tipps für rekursive CTEs

• Indizieren Sie die ParentID-Spalte – das beschleunigt den JOIN zwischen Kind und Elternteil massiv.
• Nutzen Sie OPTION (MAXRECURSION n) bei sehr tiefen Bäumen (Default 100). Passen Sie den Wert aber bewusst an.
• Vermeiden Sie Korrelationen innerhalb der CTE – die Rekursion darf nicht von äußeren Abfragen abhängig sein.
• Materialisieren Sie Zwischenergebnisse – wenn Sie denselben Unterbaum mehrmals benötigen, speichern Sie ihn in einer temporären Tabelle ab.
• Verwenden Sie CAST für Pfadspalten mit ausreichender Länge – sonst riskieren Sie einen Laufzeitfehler durch Abschneiden.

8. Praxisbeispiel: „Alle Untergruppen einer Kategorie finden“

Angenommen Sie haben eine Produktkategoriehierarchie (Elektronik → Computer → Laptops, Zubehör). Mit einer rekursiven CTE erhalten Sie alle untergeordneten Kategorien einer bestimmten Kategorie – perfekt für Navigation oder Filterung im Shop.

WITH CategoryCTE AS (
  SELECT CatID, Name, ParentID
  FROM Categories WHERE CatID = 42 -- Startkategorie 'Computer'
  UNION ALL
  SELECT c.CatID, c.Name, c.ParentID
  FROM Categories c
  INNER JOIN CategoryCTE cc ON c.ParentID = cc.CatID
)
SELECT * FROM CategoryCTE;

Das Ergebnis enthält die Startkategorie selbst sowie alle darunter liegenden Ebenen. Möchten Sie nur die Unterkategorien (ohne die Startkategorie), fügen Sie in der finalen SELECT-Abfrage eine WHERE CatID <> 42 hinzu.

9. Zusammenfassung & Fazit

Hierarchische Daten sind in SQL Server mit den vorgestellten Methoden gut abbildbar:

• Adjacency List (ParentID) ist der Standard, der durch rekursive CTEs flexibel ausgewertet werden kann.
• HierarchyID ist der native Microsoft-Ansatz für sehr leistungsfähige Teilbaumabfragen mit geringem Speicherbedarf.
• Materialized Path bietet schnelle Leseoperationen auf Kosten der Schreibgeschwindigkeit.

Für die meisten Projekte reicht die Adjacency List in Kombination mit einer rekursiven CTE völlig aus. Erst bei extremen Skalierungsanforderungen (z. B. Millionen von Knoten) lohnt der Umstieg auf hierarchyid oder eine spezielle Graphdatenbank. Wichtig ist, dass Sie das Konzept der Rekursion verinnerlichen – dann beherrschen Sie eines der mächtigsten Werkzeuge von T-SQL.

powered by dtcSoftware