Common Table Expressions (CTEs)

Wer regelmäßig mit SQL Server arbeitet, kommt irgendwann an den Punkt, an dem Abfragen länger werden als die Geduld des eigenen Query-Editors. Geschachtelte Unterabfragen, Joins über Joins, Aggregationen in zweiter und dritter Ebene – irgendwann ist der Punkt erreicht, an dem selbst der Compiler leise um Gnade bittet. Oder Abfragen lassen sich nur sinnvoll mit Rekursion löschen. Genau an dieser Stelle betreten CTEs – Common Table Expressions – die Bühne.

Was ist eine CTE überhaupt?

Eine CTE ist eine temporäre, benannte Ergebnismenge, die innerhalb einer einzelnen SQL-Anweisung definiert und verwendet werden kann. Sie verhält sich im Grunde wie eine View auf Zeit – nur eben ohne dass sie dauerhaft in der Datenbank gespeichert wird. Sie ist nur für weitere CTEs (bei Verkettung) oder spätestens für das obligatorische abschließenden DML-Statement verfügbar. Das DML-Statement kann auch ein UPDATE sein, denn ja: Ähnlich wie Views sind CTEs updatefähig.

Die Syntax ist angenehm schlicht:

;WITH UmsatzProKunde
 AS
 (
     SELECT 
         CustomerID [KundenNr],
         SUM(TotalDue) [Gesamtumsatz]
     FROM Sales.SalesOrderHeader
     GROUP BY CustomerID
 )
 SELECT 
     c.KundenNr,
     c.Gesamtumsatz
 FROM UmsatzProKunde c
 WHERE c.Gesamtumsatz > 50000;

Alternativ könnten die (eindeutigen Namen) der CTE-Spalte auch direkt angegeben werden.

WITH UmsatzProKunde ([KundenNr], [Gesaumtumsatz])
 AS
 (
     SELECT 
         CustomerID,
         SUM(TotalDue)
     FROM Sales.SalesOrderHeader
     GROUP BY CustomerID
 )
 SELECT 
     c.KundenNr,
     c.Gesamtumsatz
 FROM UmsatzProKunde c
 WHERE c.Gesamtumsatz > 50000;

Die Zeilen zwischen den Klammern definieren die CTE – also das, was du früher wahrscheinlich in eine Unterabfrage gepackt hättest. Danach wird sie behandelt, als wäre sie eine eigene Tabelle. Kein CREATE VIEW, kein temporäres Objekt – einfach nur eine elegante Lesbarkeitsschicht.

Außerdem können CTEs verkettet werden. Dies ermöglicht es, mehrere CTEs zu definieren, wobei eine CTE nur auf die zuvor definierten zugreifen kann. Die DML-Anweisung zum Schluss kann dann auf alle zugreifen.

;WITH [cte1] AS
 (
     SELECT [ID], CONCAT([Vorname], [Name]) AS [Fullname] FROM [dbo].[Personenverzeichnis]
 ),
 
 -- Die zwei CTE kann auf die erste CTE zugreifen und bestimmt
 -- die Länge des Namens
 [cte2] AS
 (
     SELECT [ID], LEN([Fullname]) AS [NameLength] FROM [cte1]    
 ),
 
 -- Die dritte CTE kann auf CTE 1 + 2 zugreifen und sucht die
 -- maximale Länge des vollständigen Namens
 [cte3] AS
 (
     SELECT MAX([NameLength]) AS [MaxLength] FROM [cte2]
 ),
 
 -- Die vierte CTE kann auf CTE 1 - 3 zugreifen und stellt
 -- alle für das Endergebnis zusammen
 [cte4] AS
 (
     SELECT [pv].[id],
         [cte1].[Fullname],
         CAST([cte2].[NameLength] AS DECIMAL) AS [NameLength],
         (SELECT [MaxLength] FROM [cte3]) AS [MaxLength]    
     FROM [dbo].[Personenverzeichnis] [pv]
     LEFT JOIN [cte1] ON [pv].[ID] = [cte1].[ID]
     LEFT JOIN [cte2] ON [pv].[ID] = [cte2].[ID]
 ),
 
 -- Die vierte CTE kann auf CTE 1 - 4 zugreifen und
 -- führte die letzte Berechnung durch, indem die Länge
 -- prozentual zur maximalen Länge berechnet wird
 [cteFinal] AS
 (
     SELECT *,
         [NameLength] / [MaxLength] AS [PercentLength]
     FROM [cte4]
 )

Die cte4 kann also auf cte1, cte2 und cte3 zugreifen, jedoch nicht auf cteFinal.

Warum nicht einfach eine Subquery?

Klar, das obige Beispiel ließe sich auch mit einer klassischen Unterabfrage lösen. Der Vorteil einer CTE zeigt sich erst, wenn du mehrere logische Teilschritte hast oder dieselbe Ergebnismenge mehrfach verwenden willst. Gerade Entwickler mit Hintergrund in einer höheren Programmiersprache dürfte das ansprechen. Es ist damit mehr als bloße Syntaxkosmetik. Lesbarkeit bedeutet Wartbarkeit, und Wartbarkeit bedeutet Zukunftssicherheit.

Ein schönes Detail: Du kannst mehrere CTEs hintereinander definieren, getrennt durch Kommas. SQL Server führt sie intern nacheinander aus, so als würdest du kleine logische Bausteine stapeln. Aber Achtung! Dabei wird nicht eine CTE erst komplett ausgeführt, bevor es weitergeht, sondern es ist mehr ein Verarbeitungsfluss. Stelle dir also bitte keine „internen temporären Tabellen“ vor, die der Reihe nach befüllt werden. Weiter unten gehe ich auf diesen Aspekt noch einmal ein.

Rekursion

Der eigentliche Zauber beginnt, wenn man rekursive CTEs einsetzt. Sie ermöglichen es, hierarchische Strukturen zu traversieren – etwa Organisationsbäume, Artikelstrukturen oder Dateipfade. Beispiel: Ein klassischer Mitarbeiter-Hierarchiebaum.

;WITH [cte]
 AS
 (
     -- Anker der Rekursion
     SELECT *, 0 AS [Level] FROM [dbo].[Personenverzeichnis] WHERE [VorgesetzterID] IS NULL
     
     UNION ALL
 
     -- Rekursion
     SELECT [pv].*, [cte].[Level]+ 1 FROM [dbo].[Personenverzeichnis] [pv] INNER JOIN [cte]
     ON [cte].[ID] = [pv].[VorgesetzterID]
 )
 SELECT * FROM [cte]
 OPTION (MAXRECURSION 10); -- 0 oder 32767 stehen für die maximale Tiefe von 32767

Diese Form der Selbstbezüglichkeit ist der Moment, in dem SQL ein klein wenig Lisp-artig wird: Eine Definition, die sich selbst verwendet, bis keine weiteren Datensätze mehr gefunden werden. Das Ergebnis ist eine geordnete Baumstruktur – ganz ohne prozedurale Schleife. Zur Vermeidung von rekursiven Explosionen dient der Hint MAXRECURSION.

Die Sache mit dem Semikolon

CTEs sind einige der wenigen Sprachfeatures, die zwingend ein Semikolon benötigen, und zwar nach der vorherigen Anweisung, wenn es eine solche gibt. Um also sicherzugehen, findet man in vielen Skripten (wie auch hier) rein prophylaktisch ein Semikolon.

Semikolon

Nötig ist es am Ende einer Anweisung in den meisten Fällen nicht. Microsoft hält sich hier einfach nicht an ANSI SQL-92. Die Ausnahmen sind CTEs und die MERGE-Anweisung. Aber: das Schreiben von Semikola zum Abschluss einer T-SQL-Anweisung unterstützt Strukturierung und Lesbarkeit. Der Autor outet sich gern als „Semikolon-Fan“

Performance: Zwischen Magie und Missverständnis

Ein häufiger Irrtum: Viele glauben, eine CTE sei eine Art temporäre Tabelle, die einmal berechnet und dann mehrfach verwendet wird. Falsch!

Eine CTE ist nur syntaktischer Zucker. Sie wird bei jeder Referenz neu aufgelöst, als wäre der Inhalt direkt in die Abfrage eingebettet. Wenn du also dieselbe CTE mehrfach im DML-Statement verwendest, führt das zu mehrfacher Auswertung – und damit potenziell zu Leistungseinbußen.

Wer Performance will, nutzt in solchen Fällen besser eine temporäre Tabelle (#Temp oder ##Temp) oder eine Table-Variable (@Variable).

CTEs glänzen in Struktur und Lesbarkeit, nicht in Wiederverwendbarkeit. Die Ausnahme hier sind rekursive CTEs: Dort wird der rekursive Teil tatsächlich iterativ verarbeitet, Zwischenergebnisse werden also intern gepuffert, bis die Bedingung nicht mehr erfüllt ist.

CTEs und moderne SQL-Server-Features

Mit den aktuelleren SQL-Server-Versionen hat sich an der Funktionsweise von CTEs wenig geändert – ihre Bedeutung aber wächst. In Kombination mit Window Functions, JSON-Ausdrücken oder APPLY-Operatoren lassen sich heute extrem komplexe Auswertungen formulieren, die früher Stored Procedures erfordert hätten.

Ein schönes Beispiel: CTE + Window Function = verständliche Trendanalyse.

;WITH Umsaetze AS
 (
     SELECT 
         OrderDate,
         SUM(TotalDue) AS Tagesumsatz
     FROM Sales.SalesOrderHeader
     GROUP BY OrderDate
 )
 SELECT 
     OrderDate,
     Tagesumsatz,
     LAG(Tagesumsatz) OVER (ORDER BY OrderDate) AS Vortag,
     Tagesumsatz - LAG(Tagesumsatz) OVER (ORDER BY OrderDate) AS Differenz
 FROM Umsaetze;

Klar strukturiert, gut lesbar – und mächtig genug, um Zeitreihen ohne Unterabfragen zu analysieren.

Fehlerquellen und Stolperfallen

Die häufigsten Missverständnisse in der Praxis:

• Namenskonflikte: Eine CTE überlagert gleichnamige Tabellen oder Views.
• Scope: Eine CTE lebt nur für die unmittelbar folgende Anweisung – zum Beispiel kennt sie ein zweites SELECT danach nicht mehr.
• ORDER BY in der CTE: Nicht erlaubt, ähnlich wie in Views.
• Mehrfachverwendung: Wie erwähnt führt jede Referenz zu einer erneuten Auswertung.
• Rekursive Explosion: Eine unendliche Rekursion ohne MAXRECURSION-Begrenzung endet in Fehler 530: „The statement terminated. The maximum recursion 100 has been exhausted.“

Vermutlich lässt sich diese Liste, wie üblich bei solchen Listen, noch um einige Punkte erweitern …

Best Practices

Zum Schluss noch ein paar Tipps:

• Sprechende Namen wählen. Eine CTE ist wie eine lokale Variable – sie sollte klar machen, was sie enthält.
• Nur so viele CTEs wie nötig. Zu viele verschachtelte Definitionen machen das Lesen schwerer, nicht leichter.
• Performance testen. Der Optimizer kann zwar erstaunlich gut arbeiten, aber bei mehrfachen Referenzen entstehen Redundanzen.
• Mit MAXRECURSION arbeiten. Besonders bei Daten mit unklarer Hierarchietiefe.
• Nicht in Prozeduren stapeln. Wenn du viele CTEs brauchst, ist es vielleicht Zeit, die Logik tatsächlich aufzuteilen.

Fazit

Eine CTE ist kein Performance-Trick und kein Ersatz für temporäre Tabellen. Sie ist eine Lesbarkeitswaffe. Sie hilft, SQL-Abfragen so zu schreiben, dass sie auch in sechs Monaten noch verstanden werden können – nicht nur von Maschinen, sondern von Menschen. CTEs sind der Beweis, dass man selbst in deklarativer Sprache strukturiert denken kann – fast schon ein Stück funktionale Eleganz mitten im relationalen Alltag. Außerdem bändigen sie Rekursionen.