Mittendrin.

Zurück

Flurfunk der eXperts.

Hier erfahren Sie mehr über eXperts, Technologien und das wahre Leben in der SDX.

T-SQL Deep Dive: Common Table Expressions

20.03.201408:00 Uhr , Markus Schwamberger

Dies ist Teil einer Serie über nützliche jedoch wenig bekannte SQL Server Funktionen.

Eine Übersicht über bisherige Artikel ist hier zu finden.

Im dritten Teil möchte ich den allgemeinen Tabellenausdruck (Common Table Expressions kurz CTE) vorstellen. Eine CTE ist ein temporäres benamtes Ergebnisset welche nur für die Dauer der Abfrage existiert. Man kann sich eine CTE als eine temporäre View vorstellen. Mit CTE können komplexe Abfragen in kleinere, logische Komponentenblöcke zerlegt werden. Dies ermöglicht übersichtlicheren und somit wartbareren Code. Darüber hinaus sind mit CTE rekursive Abfragen mit einer einfachen und deklarativen Syntax möglich. CTE wurden bereits mit SQL Server 2005 eingeführt und sind Bestandteil des ANSI SQL99-Standards.

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

image

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
)
 
Die Definition einer rekursiven CTE besteht aus zwei Teilen, zuerst wird der so genannte Anker definiert. Dies ist der Basisknoten der Abfrage. Es folgt die Definition der eigentlichen Rekursion mit Verweis auf sich selbst.
 
Eine rekursive CTE kann man sich als eine endlose Aneinanderreihung von UNION ALL Abfragen vorstellen. Unser Beispiel würde dann so aussehen:
SELECT Anker
UNION ALL
SELECT NachfolgendeKnoten1
FROM Anker Query
UNION ALL
SELECT NachfolgendeKnoten2
FROM NachfolgendeKnoten1 Query
UNION ALL
SELECT NachfolgendeKnoten3
FROM NachfolgendeKnoten2 Query
...
 
Folgende Befehle sind in rekursiven CTE nicht erlaubt:
  • 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

image

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.

Tags: SQL Server

0 Kommentare
Dein Kommentar wartet auf Freischaltung.

Artikel kommentieren

Zurück

Tag Cloud