De Algemene tabelexpressies (CTE) werden geïntroduceerd in standaard SQL om verschillende klassen SQL-query's te vereenvoudigen waarvoor een afgeleide tabel gewoon niet geschikt was. CTE is geïntroduceerd in SQL Server 2005. De Common Table Expression (CTE) is een tijdelijke benoemde resultatenset waarnaar u kunt verwijzen binnen een SELECTEER , INVOEGEN , UPDATE , of VERWIJDEREN stelling. U kunt ook een CTE gebruiken in een CREATE-weergave, als onderdeel van de SELECT-query van de weergave. Bovendien kunt u vanaf SQL Server 2008 een CTE toevoegen aan de nieuwe MERGE-instructie.

Waarom hebben we CTE nodig op de SQL-server?

Een gemeenschappelijke tabel [verouderd] CTE is een krachtige SQL constructie die zoekopdrachten helpt vereenvoudigen. CTE's fungeren als virtuele tabellen (met records en kolommen) die worden gemaakt tijdens het uitvoeren van de query, worden gebruikt door de query en worden verwijderd nadat de query is uitgevoerd.

Het gebruik van de CTE

We kunnen CTE's definiëren door een WITH-clausule toe te voegen direct vóór de SELECT-, INSERT-, UPDATE-, DELETE- of MERGE-instructie. De WITH-clausule kan een of meer CTE's bevatten, gescheiden door komma's.

voorbeelden van dfa-automaten

Syntaxis:

[MET [, …]]

::=

cte_name [(kolomnaam [, …])]

AS (cte_query)

Argument

1. Expressienaam: Een geldige ID voor een algemene tabelexpressie. De naam_expressie moet verschillen van de namen van andere algemene tabelexpressies die in dezelfde WITH-clausule zijn gedefinieerd, maar de naam_expressie kan hetzelfde zijn als de naam van de basistabel of -weergave. Alle verwijzingen naar expression_name in de query gebruiken de algemene tabelexpressie in plaats van het basisobject.
2. Kolomnaam: Specificeert een kolomnaam in een algemene tabelexpressie. Dubbele namen binnen één enkele CTE-definitie zijn niet toegestaan. Het aantal kolomnamen moet overeenkomen met het aantal kolommen in de resultatenset van CTE_query_definition. Een lijst met kolomnamen is alleen optioneel als alle resulterende kolommen in de querydefinitie verschillende namen hebben.
3. CTE_QueryDefinitie: Specificeert een SELECT-instructie waarvan de resultaatset voldoet aan een algemene tabelexpressie. De SELECT-instructie voor CTE_query_defining moet aan dezelfde vereisten voldoen als het maken van een weergave, behalve dat een CTE geen andere CTE kan definiëren. Zie de sectie Opmerkingen en CREATE VIEW (Transact-SQL) voor meer informatie. Als er meerdere CTE_query_settings zijn gedefinieerd, moeten de querydefinities worden gecombineerd met een van de set-operators UNION ALL, UNION, EXCEPT of INTERSECT.
   

Regels voor het definiëren en gebruiken van recursieve algemene tabelexpressies

De volgende richtlijnen zijn van toepassing op het definiëren van recursieve gemeenschappelijke tabelexpressies:

dharmendra-leeftijd

1. Een recursieve CTE-definitie moet ten minste twee CTE-querydefinities bevatten, een ankerlid en een recursief lid. U kunt meerdere anker- en recursieve leden definiëren. Alle querydefinities van ankerleden moeten echter vóór de eerste recursieve liddefinitie worden geplaatst. Alle CTE-querydefinities zijn ankerleden, tenzij ze verwijzen naar de CTE zelf.
2. Ankerelementen moeten worden gecombineerd met een van de volgende setoperatoren: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL is de enige geldige set-operator tussen het laatste ankerlid en het eerste recursieve lid bij het samenvoegen van meerdere recursieve leden. Anker- en recursieve elementen moeten hetzelfde aantal kolommen hebben.
3. Het gegevenstype van de kolommen van het recursieve lid moet hetzelfde zijn als het gegevenstype van de corresponderende kolom van het ankerlid.
4. De volgende items zijn niet toegestaan ​​in haar CTE_query_definition voor recursieve leden:

Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

Een recursieve gemeenschappelijke tabelexpressie maken

Een recursieve CTE is een CTE die naar zichzelf verwijst binnen die CTE. De recursieve CTE is handig bij het werken met hiërarchische gegevens, aangezien de CTE blijft uitvoeren totdat de query de volledige hiërarchie retourneert.

Een typisch voorbeeld van hiërarchische gegevens is een tabel met een lijst met werknemers. Voor iedere medewerker geeft de tabel een verwijzing naar de leidinggevende van die persoon. Die referentie is zelf een werknemers-ID binnen dezelfde tabel. U kunt een recursieve CTE gebruiken om de hiërarchie van werknemersgegevens weer te geven.

alfabet naar nummer

Als een CTE verkeerd wordt aangemaakt, kan deze in een oneindige lus terechtkomen. Om dit te voorkomen, kan de MAXRECURSION-hint worden toegevoegd aan de OPTION-clausule van de primaire SELECT-, INSERT-, UPDATE-, DELETE- of MERGE-instructie.

Er wordt een tabel gemaakt:

CREATE TABLE Employees (  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL )  INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)>

Nadat de tabel Employees is gemaakt, wordt de volgende SELECT-instructie gemaakt, die wordt voorafgegaan door een WITH-clausule die een CTE met de naam cteReports bevat:

WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (  SELECT EmployeeID, FirstName, LastName, ManagerID, 1  FROM Employees  WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,   r.EmpLevel + 1  FROM Employees e  INNER JOIN cteReports r  ON e.ManagerID = r.EmpID  ) SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees   WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports  ORDER BY EmpLevel, MgrID>

CTE's kunnen dus handig zijn wanneer u tijdelijke resultaatsets moet genereren die toegankelijk zijn via een SELECT-, INSERT-, UPDATE-, DELETE- of MERGE-instructie.

Functies en beperkingen van algemene tabelexpressies in Azure

De huidige implementatie van CTE's in Azure Synapse Analytics en Analytics Platform System (PDW) heeft de volgende functies en beperkingen:

1. Een CTE kan alleen worden opgegeven in een SELECT-instructie.
2. Een CTE kan alleen worden opgegeven in een CREATE VIEW-instructie.
3. Een CTE kan alleen worden opgegeven in een CREATE TABLE AS SELECT (CTAS)-instructie.
4. Een CTE kan alleen worden opgegeven in een CREATE REMOTE TABLE AS SELECT (CRTAS)-instructie.
5. Een CTE kan alleen worden opgegeven in een CREATE EXTERNAL TABLE AS SELECT (CETAS)-instructie.