SQL Server Common Tabellenausdruck

Der Common Table Expression oder das CTE ist das benannte Ergebnissatz, das in SQL Server 2005 eingeführt wurde. Der Ausdruck der gemeinsamen Tabelle fungiert als virtuelle Tabelle mit Datensätzen und Spalten, die während der Ausführung einer Abfrage mit CTE erstellt und nach Abschluss der Abfrage freigegeben wird. Es kann in einer beliebigen Auswahl, Einfügung, Aktualisierung oder Löschung referenziert werden. Dies wird auch verwendet, um eine Ansicht zu erstellen.

CTE kann durch die folgende Syntax definiert werden.

[MIT [… ]]
Name von CTE [(Name der Spalten [,…])]
As (Abfrage von CTE)
Wählen Sie * aus CTE aus

Beispiel:

Mit CTE_NAME (Spalte1, Spalte2, Spalte3)
Als
(
Wählen Sie Spalte1, Spalte2, Spalte3
Aus Tabelle 1
Wobei Spalte1> 500
)

Nach dem Beispiel können wir nach der Definition des CTE CTE_NAME den CTE unmittelbar nach der Definition als Tabelle verwenden. Unten ist ein Beispiel:

Wählen Sie aus cte_name

Es wird die Ausgabe von drei Spalten, Spalte1, Spalte2 und Spalte3 zurückgegeben.

Es kann auch im Einfügen, Löschen, Aktualisieren und Zusammenführen verwendet werden. Wir werden ein Beispiel für jeweils unten zeigen.

Mehrfach CTE

Mehrfach CTE kann in einer einzelnen Abfrage verwendet werden.

Mit CTE_NAME1 (Spalte1, Spalte2, Spalte3)
Als
(
Wählen Sie Spalte1, Spalte2, Spalte3
Aus Tabelle 1
Wobei Spalte1> 100
)
ALS
(
Wählen Sie * aus cte_name2 aus
wobei Spalte2> 200
)
Wählen Sie * aus cte_name2 aus

Die obige Abfrage gibt die Datensätze aus der Tabelle Tabelle 1 zurück, wobei Spalte1 größer als 100 ist und Spalte2 größer als 200 ist.

Löschen mit CTE

CTE kann sehr praktisch sein, um Datensätze aus einer Tabelle zu löschen.

Mit CTE_NAME (Spalte1, Spalte2, Spalte3)
Als
(
Wählen Sie Spalte1, Spalte2, Spalte3
Aus Tabelle 1
Wobei Spalte1> 100
)
Löschen Sie aus CTE_NAME

Die obige Anweisung löscht die Datensätze aus der Basistabelle: Tabelle Tabelle 1, wobei der Wert von Spalte1 mehr als 100 beträgt.

Dies ist auch die effiziente Möglichkeit, doppelte Einträge aus einer Tabelle zu beseitigen. Unten ist das Beispiel.

Mit CTE_NAME (ID, Spalte1, Spalte2, Spalte3, RN)
Als
(
Wählen Sie ID, Spalte1, Spalte2, Spalte3, Row_Number () Over (Partition nach ID -Bestellung nach ID) als RN aus
Aus Tabelle 1
)
Löschen Sie aus CTE_NAME
Wo cte_name. Rn> 1

Dadurch werden alle doppelten Zeilen aus der Tabelle Tabelle 1 gelöscht.

Einfügen mit CTE

Wir können einen bestimmten Datensatz einfügen, der in einem CTE in eine andere Tabelle definiert ist. Schauen Sie sich das folgende Beispiel an.

Mit cte_insert (id, spalte1, spalte2, spalte3)
Als
(
Wählen Sie ID, Spalte1, Spalte2, Spalte3
Aus Tabelle 1
Wobei Spalte1> 200
)
/* zum Einfügen in eine vorhandene Tabelle dest_table*/
In dest_table einfügen (Spalte1, Spalte2, Spalte3)
Wählen Sie Spalte1, Spalte2, Spalte3 aus cte_insert aus
/ * Zum Erstellen einer neuen Tabelle dest_table_new und einfügen die Daten des CTE */
Wählen Sie Spalte1, Spalte2, Spalte3
In dest_table_new

Die obige Anweisung erstellt die Tabelle mit den drei Spalten- Spalte1, Spalte2, Spalte3 und fügen Sie Daten ein.

Aktualisieren Sie mit CTE

Das Konzept der Aktualisierung mit CTE entspricht dem Einfügen und Löschen. Lassen Sie uns unten ein Beispiel überprüfen.

Mit CTE_UPDATE (ID, Spalte1, Spalte2, Spalte3)
Als
(
Wählen Sie ID, Spalte1, Spalte2, Spalte3
Aus Tabelle 1
Wobei Spalte1> 200
)
/* Aktualisieren Sie die Basistabelle- Tabelle 1 von CTE, um den Wert von Spalte1 um 100*/zu erhöhen
Aktualisieren Sie CTE_UPDATE
Setzen Sie Spalte1 = Spalte1+100
/*Aktualisieren Sie eine andere Tabelle - dest_table unter Verwendung des Werts von CTE*/
Update a
einstellen a.Spalte1 = b.Spalte1
von dest_table a
beitreten Sie CTE_UPDATE b
auf einen.id = b.Ausweis

Zusammenführen mit CTE

Weitere Informationen finden Sie im folgenden Beispiel für ein besseres Verständnis.

Mit SRC_CTE (ID, Spalte1, Spalte2, Spalte3)
ALS
(
Wählen Sie ID, Spalte1, Spalte2, Spalte3 aus src_table aus
)
VERSCHMELZEN
TGT_TBL als Ziel
Verwenden Sie src_cte als Quelle
Am Ziel.ID = Quelle.Ausweis)
Wenn dann abgestimmt
Update Set Ziel.Spalte1 = Quelle.Spalte1,
Ziel.Spalte2 = Quelle.Spalte2,
Ziel.Spalte3 = Quelle.Spalte3
Wenn dann nicht übereinstimmt
Insert (Spalten1, Spalte2, Spalten3) Werte (Quelle.Spalte1, Quelle.Spalte2, Quelle.Spalte3);

In der obigen Abfrage versuchen wir, Daten schrittweise vom src_table in die TGT_Table zu laden.

Wie CTE, TEMP -Tabelle und TEMP -Variable in SQL Server aufgeschoben werden?

Aus den letzten Beispielen lernen wir die Verwendung von CTE kennen und haben eine klare Vorstellung davon, was CTE ist. Der Unterschied zwischen CTE IS und TEMP -Tabelle und TEMP -Variable beträgt nun:

• • CTE benötigt immer Speicher, aber Tempabellen benötigen eine Festplatte. Die Tabellenvariable verwendet beide. Daher sollten wir CTE nicht verwenden, wenn mehr Datenvolumen vorhanden sind.
  • Der Umfang der Tabellenvariablen ist nur für die Charge und der Umfang der Temp -Tabelle für die Sitzung und der Umfang von CTE ist nur für die Abfrage gilt.

Abschluss

CTE kann nützlich sein, wenn Sie eine temporäre Ergebnismenge generieren müssen, und es kann in der Auswahl, Einfügen, Einfügen, Aktualisieren, Löschen und Zusammenführungsanweisungen zugegriffen werden. Es kann in Bezug auf CPU und Speicherverbrauch viel optimiert werden.