Alle definierten CTE werden zur Ausführung vom Query Optimizer in die Abfrage eingeflochten als wären Sie normale Unterabfragen. Es entsteht also kein Overhead wie bei temporären Tabellen. Der erstellte Ausführungsplan kann sich aber von Ausführungsplan mit Unterabfragen unterscheiden. Bei kritischen Abfragen sollten also Performancetest durchgeführt werden.

Syntax:

WITH CTE_Name (optionale Spaltennamen) AS
(
    SELECT Statement
)

Eine CTE startet immer mit "WITH" gefolgt vom Alias der CTE und einer Liste an Spalten. Die Liste der Spaltennamen ist optional, wenn eindeutige Namen für alle Spalten der Abfragedefinition definiert wurden.

Für CTE gibt es eine ganze Reihe von Limitierungen. Folgende Befehle sind beispielsweise in einer nicht rekursiven CTE Statement nicht erlaubt:

• ORDER BY (ausser es wurde ein SELECT TOP definiert)

• INTO

• OPTION mit Queryhints

• FOR BROWSE

Eine Abfrage kann mehr als eine CTE enthalten, mehrere CTE können mit Komma getrennt definiert werden. Eine CTE kann eine andere CTE referenzieren und eine CTE kann auch sich selbst referenzieren (dazu später mehr).

Die CTE muss im unmittelbar nächsten Statement konsumiert werden, sonst gibt es einen Fehler:

WITH BeispielCTE AS (
SELECT [LoginID], [OrganizationLevel], [JobTitle]
  FROM [HumanResources].[Employee]
)
SELECT 'Andere Abfrage'
SELECT * FROM BeispielCTE

 
Msg 422, Level 16, State 4, Line 8

Common table expression defined but not used.

Beispiele:

Für die Beispiele wird die AdventureWorks2012 Datenbank verwendet. Als Erstes wollen wir eine Liste aller Mitarbeiter erstellen deren Vorgesetzter den Titel "Engineering Manager" trägt:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM     HumanResources.Employee AS e INNER JOIN
                  Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
WHERE  (e.OrganizationNode.GetAncestor(1) =
                      (SELECT OrganizationNode
                       FROM      HumanResources.Employee
                       WHERE   (JobTitle = 'Engineering Manager')))

Mit einer CTE können wir die Unterabfrage auslagern:

WITH BeispielCTE AS (
    SELECT OrganizationNode
    FROM HumanResources.Employee
    WHERE (JobTitle = 'Engineering Manager')
)
SELECT p.FirstName, p.LastName, e.JobTitle
FROM     HumanResources.Employee AS e INNER JOIN
                  Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID

INNER JOIN BeispielCTE ON e.OrganizationNode.GetAncestor(1) = BeispielCTE.OrganizationNode

Zugegeben, bei diesem überschaubaren Beispiel ist der Übersichtlichkeitsgewinn nicht gerade Beeindruckend. Bei komplexen Abfragen mit mehreren Unterabfragen helfen CTE allerdings enorm die Lesbarkeit zu erhöhen. Gerade auch durch die Möglichkeit eine CTE in einer CTE zu referenzieren (Unterabfrage in einer Unterabfrage).

Rekursion

Wie bereits mehrfach erwähnt kann eine CTE auch sich selbst referenzieren. Damit ist es möglich Werte einer vorhergehende Ebene abzufragen und somit eine Rekursion zu erzeugen. Funktionen wie ROW_NUMBER werden dabei immer auf der aktuellen Rekursionsebene ausgeführt und nicht auf alle Daten.

Syntax:

WITH BeispielCTE AS (
    SELECT Anker
    UNION ALL
    SELECT NachfolgendeKnoten
    FROM BeispielCTE
    WHERE RekursionsBedingung
)

SELECT Anker
UNION ALL
SELECT NachfolgendeKnoten1
FROM Anker Query
UNION ALL
SELECT NachfolgendeKnoten2
FROM NachfolgendeKnoten1 Query
UNION ALL
SELECT NachfolgendeKnoten3
FROM NachfolgendeKnoten2 Query
...

• SELECT DISTINCT

• GROUP BY

• PIVOT (In Versionen kleiner 2012)

• HAVING

• TOP

• LEFT, RIGHT & OUTER JOIN

Beispiel:

Als Beispiel lösen wir das FizzBuzz Problem mit einer einfachen SQL Abfrage:

WITH RekursiveCTE AS (
  SELECT 1 AS Nummer
  UNION ALL
  SELECT Nummer + 1
  FROM RekursiveCTE
  WHERE Nummer < 100
)
SELECT
  CASE 
    WHEN Nummer % 3 = 0 AND Nummer % 5 = 0 THEN 'FizzBuzz'
    WHEN Nummer % 3 = 0 THEN 'Fizz'
    WHEN Nummer % 5 = 0 THEN 'Buzz'
    ELSE CONVERT(VARCHAR(3), Nummer)
  END
FROM RekursiveCTE
ORDER BY Nummer

Bei Rekursion besteht immer auch die Gefahr einer unendlichen Schleife. Um dies zu verhindern kann mit dem Query Hint MaxRecursion die maximal Anzahl an Rekursionen definiert werden. Standardmäßig ist dieser auf 100 Rekursionen eingestellt. MaxRecursion 0 entspricht unendlich vielen Rekursionen. Wird der definierte Wert überschritten gibt es einen Fehler:

WITH RekursiveCTE AS (
  SELECT 1 AS Nummer
  UNION ALL
  SELECT Nummer + 1
  FROM RekursiveCTE
  WHERE Nummer < 100
)
SELECT
  CASE 
    WHEN Nummer % 3 = 0 AND Nummer % 5 = 0 THEN 'FizzBuzz'
    WHEN Nummer % 3 = 0 THEN 'Fizz'
    WHEN Nummer % 5 = 0 THEN 'Buzz'
    ELSE CONVERT(VARCHAR(3), Nummer)
  END
FROM RekursiveCTE
ORDER BY Nummer
OPTION (MAXRECURSION 25)

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 25 has been exhausted before statement completion.

Fazit

Common Table Expressions helfen beim Zerlegen komplexer Abfragen in mehrere kleinere Abfragen. Sie ermöglichen es wesentlich übersichtlichere und damit wartbarere  Abfragen zu schreiben. Als weiteres Feature lassen sich mit CTE rekursive Abfragen in T-SQL auch ohne Verwendung eines Cursors einfach umzusetzen.