TechCodeview

Wir werden die Common Table Expressions oder CTEs des SQL Servers verwenden, um komplexe Verknüpfungen und Unterabfragen zu vereinfachen. Es bietet auch eine Möglichkeit, hierarchische Daten abzufragen, beispielsweise eine Organisationshierarchie. Dieser Artikel bietet einen vollständigen Überblick über CTE, CTE-Typen, Vor- und Nachteile sowie deren Verwendung in SQL Server.

Was ist CTE in SQL Server?

Ein CTE (Common Table Expression) ist eine einmalige Ergebnismenge, die nur für die Dauer der Abfrage vorhanden ist . Es ermöglicht uns, auf Daten innerhalb des Ausführungsbereichs einer einzelnen SELECT-, INSERT-, UPDATE-, DELETE-, CREATE VIEW- oder MERGE-Anweisung zu verweisen. Es ist temporär, da das Ergebnis nirgendwo gespeichert werden kann und verloren geht, sobald die Ausführung einer Abfrage abgeschlossen ist. Es kam erstmals mit der SQL Server 2005-Version. Ein DBA bevorzugte immer die Verwendung von CTE als Alternative zu einer Unterabfrage/Ansicht. Sie folgen dem ANSI SQL 99-Standard und sind SQL-kompatibel.

CTE-Syntax in SQL Server

Die CTE-Syntax umfasst einen CTE-Namen, eine optionale Spaltenliste und eine Anweisung/Abfrage, die den Common Table Expression (CTE) definiert. Nachdem wir den CTE definiert haben, können wir ihn als Ansicht in einer SELECT-, INSERT-, UPDATE-, DELETE- und MERGE-Abfrage verwenden.

Das Folgende ist die grundlegende Syntax von CTE in SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

In dieser Syntax:

• Wir haben zunächst den CTE-Namen angegeben, auf den später in einer Abfrage verwiesen wird.
• Der nächste Schritt besteht darin, eine Liste mit durch Kommas getrennten Spalten zu erstellen. Es stellt sicher, dass die Anzahl der Spalten in den CTE-Definitionsargumenten und die Anzahl der Spalten in der Abfrage gleich sein müssen. Wenn wir die Spalten der CTE-Argumente nicht definiert haben, werden die Abfragespalten verwendet, die den CTE definieren.
• Danach verwenden wir das Schlüsselwort AS nach dem Ausdrucksnamen und definieren dann eine SELECT-Anweisung, deren Ergebnismenge den CTE füllt.
• Schließlich verwenden wir den CTE-Namen in einer Abfrage wie der SELECT-, INSERT-, UPDATE-, DELETE- und MERGE-Anweisung.

Dies sollte beim Schreiben der CTE-Abfragedefinition berücksichtigt werden; Wir können die folgenden Klauseln nicht verwenden:

1. ORDER BY, es sei denn, Sie verwenden es auch als TOP-Klausel
2. HINEIN
3. OPTION-Klausel mit Abfragehinweisen
4. ZUM DURCHSUCHEN

Das folgende Bild ist die Darstellung der CTE-Abfragedefinition.

Hier ist der erste Teil ein CTE-Ausdruck, der eine SQL-Abfrage enthält, die unabhängig in SQL ausgeführt werden kann. Und der zweite Teil ist die Abfrage, die den CTE verwendet, um das Ergebnis anzuzeigen.

Beispiel

Lassen Sie uns anhand verschiedener Beispiele verstehen, wie CTE in SQL Server funktioniert. Hier verwenden wir eine Tabelle ' Kunde ' für eine Demonstration. Angenommen, diese Tabelle enthält die folgenden Daten:

In diesem Beispiel lautet der CTE-Name customer_in_newyork , gibt die Unterabfrage, die den CTE definiert, die drei Spalten zurück Kundenname, E-Mail, Und Zustand . Infolgedessen gibt der CTE „customers_in_newyork“ alle Kunden zurück, die im Bundesstaat New York leben.

Nachdem wir den CTE „customers_in_newyork“ definiert haben, haben wir ihn in der referenziert WÄHLEN Kontoauszug, um die Daten der Kunden zu erhalten, die in New York ansässig sind.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

Nach der Ausführung der obigen Anweisung wird die folgende Ausgabe ausgegeben. Hier sehen wir, dass das Ergebnis nur die Kundeninformationen zurückgibt, die sich im Bundesstaat New York befinden.

Mehrere CTE

In einigen Fällen müssen wir mehrere CTE-Abfragen erstellen und diese zusammenführen, um die Ergebnisse anzuzeigen. In diesem Szenario können wir das Konzept mehrerer CTEs verwenden. Wir müssen den Kommaoperator verwenden, um mehrere CTE-Abfragen zu erstellen und sie in einer einzigen Anweisung zusammenzuführen. Dem Kommaoperator „,“ muss der CTE-Name vorangestellt werden, um mehrere CTE zu unterscheiden.

Mehrere CTEs helfen uns bei der Vereinfachung komplexer Abfragen, die schließlich zusammengefügt werden. Jedes komplexe Teil hatte seinen eigenen CTE, auf den dann außerhalb der WITH-Klausel verwiesen und dieser verbunden werden konnte.

HINWEIS: Die Definition mehrerer CTEs kann mit UNION, UNION ALL, JOIN, INTERSECT oder EXCEPT definiert werden.

Die folgende Syntax erklärt es klarer:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

Beispiel

Lassen Sie uns verstehen, wie mehrere CTE in SQL Server funktionieren. Hier verwenden wir das obige ' Kunde ' Tisch für eine Demonstration.

In diesem Beispiel haben wir die beiden CTE-Namen definiert customer_in_newyork Und Kunden_in_Kalifornien . Anschließend wird der CTE mit der Ergebnismenge der Unterabfragen dieser CTEs gefüllt. Abschließend verwenden wir die CTE-Namen in einer Abfrage, die alle Kunden zurückgibt, die sich in befinden New York Und Bundesstaat Kalifornien .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

New York und Kalifornien.

Warum brauchen wir CTE?

Wie Datenbankansichten und abgeleitete Tabellen können CTEs das Schreiben und Verwalten komplexer Abfragen erleichtern, indem sie sie lesbarer und einfacher machen. Wir können diese Eigenschaft erreichen, indem wir die komplexen Abfragen in einfache Blöcke zerlegen, die beim Umschreiben der Abfrage wiederverwendet werden können.

Einige seiner Anwendungsfälle sind unten aufgeführt:

• Dies ist nützlich, wenn wir eine abgeleitete Tabelle mehrmals innerhalb einer einzelnen Abfrage definieren müssen.
• Dies ist nützlich, wenn wir eine Alternative zu einer Ansicht in der Datenbank erstellen müssen.
• Dies ist nützlich, wenn wir dieselbe Berechnung mehrmals für mehrere Abfragekomponenten gleichzeitig durchführen müssen.
• Dies ist nützlich, wenn wir Ranking-Funktionen wie ROW_NUMBER(), RANK() und NTILE() verwenden müssen.

Einige seiner Vorteile sind unten aufgeführt:

groovige Computersprache

• CTE erleichtert die Codewartung.
• CTE erhöht die Lesbarkeit des Codes.
• Es erhöht die Leistung der Abfrage.
• CTE ermöglicht die einfache Implementierung rekursiver Abfragen.

CTE-Typen in SQL Server

SQL Server unterteilt die CTE (Common Table Expressions) in zwei große Kategorien:

1. Rekursiver CTE
2. Nicht rekursiver CTE

Rekursiver CTE

Ein allgemeiner Tabellenausdruck ist als rekursiver CTE bekannt, der auf sich selbst verweist. Sein Konzept basiert auf Rekursion, die definiert ist als „ die wiederholte Anwendung eines rekursiven Prozesses oder einer rekursiven Definition .' Wenn wir eine rekursive Abfrage ausführen, wird eine Teilmenge der Daten wiederholt durchlaufen. Es wird einfach als eine Abfrage definiert, die sich selbst aufruft. Irgendwann gibt es eine Endbedingung, sie nennt sich also nicht unendlich.

Ein rekursiver CTE muss einen haben UNION ALLE -Anweisung und eine zweite Abfragedefinition, die auf den CTE selbst verweist, um rekursiv zu sein.

Beispiel

Lassen Sie uns verstehen, wie rekursiver CTE in SQL Server funktioniert. Betrachten Sie die folgende Aussage: erzeugt eine Reihe der ersten fünf ungeraden Zahlen:

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Dieser CTE liefert die folgende Ausgabe, in der wir die Hierarchie der Mitarbeiterdaten sehen können:

Nicht rekursiver CTE

Ein allgemeiner Tabellenausdruck, der nicht auf sich selbst verweist, wird als nicht rekursiver CTE bezeichnet. Ein nicht rekursiver CTE ist einfach und leichter zu verstehen, da er nicht das Konzept der Rekursion verwendet. Gemäß der CTE-Syntax beginnt jede CTE-Abfrage mit einem „ Mit '-Klausel, gefolgt vom CTE-Namen und der Spaltenliste, dann AS mit Klammern.

Nachteile von CTE

Im Folgenden sind die Einschränkungen bei der Verwendung von CTE in SQL Server aufgeführt:

• CTE-Mitglieder können keine Schlüsselwortklauseln wie „Distinct“, „Group By“, „Having“, „Top“, „Joins“ usw. verwenden.
• Der CTE kann vom rekursiven Mitglied nur einmal referenziert werden.
• Wir können die Tabellenvariablen und CTEs nicht als Parameter in gespeicherten Prozeduren verwenden.
• Wir wissen bereits, dass der CTE anstelle einer Ansicht verwendet werden könnte, ein CTE kann jedoch nicht verschachtelt werden, während Ansichten dies können.
• Da es sich lediglich um eine Verknüpfung für eine Abfrage oder Unterabfrage handelt, kann es nicht in einer anderen Abfrage wiederverwendet werden.
• Die Anzahl der Spalten in den CTE-Argumenten und die Anzahl der Spalten in der Abfrage müssen gleich sein.