TechCodeview

We zullen de Common Table Expressions of CTE's van SQL Server gebruiken om complexe joins en subquery's eenvoudiger te maken. Het biedt ook een manier om hiërarchische gegevens op te vragen, zoals een organisatiehiërarchie. Dit artikel geeft een compleet overzicht van CTE, soorten CTE, voordelen en nadelen en hoe u deze kunt gebruiken in SQL Server.

Wat is CTE in SQL Server?

Een CTE (Common Table Expression) is een eenmalige resultatenset die alleen bestaat voor de duur van de query . Hiermee kunnen we verwijzen naar gegevens binnen het uitvoeringsbereik van één enkele SELECT-, INSERT-, UPDATE-, DELETE-, CREATE VIEW- of MERGE-instructie. Het is tijdelijk omdat het resultaat nergens kan worden opgeslagen en verloren zal gaan zodra de uitvoering van een query is voltooid. Het kwam voor het eerst met de SQL Server 2005-versie. Een DBA gaf er altijd de voorkeur aan om CTE te gebruiken als alternatief voor een subquery/view. Ze volgen de ANSI SQL 99-standaard en zijn SQL-compatibel.

CTE-syntaxis in SQL Server

De CTE-syntaxis bevat een CTE-naam, een optionele kolomlijst en een instructie/query die de algemene tabelexpressie (CTE) definieert. Nadat we de CTE hebben gedefinieerd, kunnen we deze gebruiken als weergave in een SELECT-, INSERT-, UPDATE-, DELETE- en MERGE-query.

Het volgende is de basissyntaxis van CTE in SQL Server:

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

In deze syntaxis:

• We hebben eerst de CTE-naam opgegeven waarnaar later in een query zal worden verwezen.
• De volgende stap is het maken van een lijst met door komma's gescheiden kolommen. Het zorgt ervoor dat het aantal kolommen in de CTE-definitieargumenten en het aantal kolommen in de query hetzelfde moeten zijn. Als we de kolommen van de CTE-argumenten niet hebben gedefinieerd, worden de querykolommen gebruikt die de CTE definiëren.
• Daarna gebruiken we het trefwoord AS na de expressienaam en definiëren we vervolgens een SELECT-instructie waarvan de resultatenset de CTE vult.
• Ten slotte zullen we de CTE-naam gebruiken in een query zoals SELECT, INSERT, UPDATE, DELETE en MERGE-instructie.

Houd er rekening mee bij het schrijven van de CTE-querydefinitie; we kunnen de volgende clausules niet gebruiken:

1. ORDER BY tenzij u ook de TOP-clausule gebruikt
2. NAAR BINNEN
3. OPTION-clausule met queryhints
4. VOOR BLADEREN

De onderstaande afbeelding is de weergave van de CTE-querydefinitie.

Hier is het eerste deel een CTE-expressie die een SQL-query bevat die onafhankelijk in SQL kan worden uitgevoerd. En het tweede deel is de query die de CTE gebruikt om het resultaat weer te geven.

Voorbeeld

Laten we aan de hand van verschillende voorbeelden begrijpen hoe CTE werkt in SQL Server. Hier gaan we een tabel gebruiken ' klant ' voor een demonstratie. Stel dat deze tabel de volgende gegevens bevat:

In dit voorbeeld is de CTE-naam klanten_in_newyork , retourneert de subquery die de CTE definieert de drie kolommen klantnaam, e-mailadres, En staat . Als gevolg hiervan zullen de CTE customers_in_newyork alle klanten retourneren die in de staat New York wonen.

Nadat we de CTE customers_in_newyork hebben gedefinieerd, hebben we ernaar verwezen in de SELECTEER verklaring om de gegevens te verkrijgen van de klanten die zich in New York bevinden.

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

Na het uitvoeren van de bovenstaande instructie zal het de volgende uitvoer opleveren. Hier kunnen we zien dat het resultaat alleen die klantinformatie retourneert die zich in de staat New York bevindt.

Meerdere CTE

In sommige gevallen moeten we meerdere CTE-query's maken en deze samenvoegen om de resultaten te bekijken. In dit scenario kunnen we het concept van meerdere CTE's gebruiken. We moeten de komma-operator gebruiken om meerdere CTE-query's te maken en deze samen te voegen tot één enkele instructie. De komma-operator ',' moet worden voorafgegaan door de CTE-naam om meerdere CTE's te onderscheiden.

Meerdere CTE's helpen ons bij het vereenvoudigen van complexe vragen die uiteindelijk worden samengevoegd. Elk complex stuk had zijn eigen CTE, waarnaar vervolgens kon worden verwezen en die buiten de WITH-clausule konden worden samengevoegd.

OPMERKING: De definitie van meerdere CTE's kan worden gedefinieerd met UNION, UNION ALL, JOIN, INTERSECT of EXCEPT.

De onderstaande syntaxis legt het duidelijker uit:

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

Voorbeeld

Laten we begrijpen hoe meerdere CTE werken in SQL Server. Hier gaan we het bovenstaande gebruiken ' klant ' tafel voor een demonstratie.

In dit voorbeeld hebben we de twee CTE-namen gedefinieerd klanten_in_newyork En klanten_in_Californië . Vervolgens vult de resultaatset van subquery's van deze CTE's de CTE in. Ten slotte zullen we de CTE-namen gebruiken in een zoekopdracht die alle klanten retourneert die zich in de regio bevinden New York En Staat Californië .

 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; 

De staat New York en Californië.

Waarom hebben we CTE nodig?

Net als databaseweergaven en afgeleide tabellen kunnen CTE's het schrijven en beheren van complexe query's eenvoudiger maken door ze leesbaarder en eenvoudiger te maken. We kunnen dit kenmerk bereiken door de complexe query's op te splitsen in eenvoudige blokken die kunnen worden hergebruikt bij het herschrijven van de query.

Enkele van de gebruiksscenario's worden hieronder gegeven:

• Dit is handig als we een afgeleide tabel meerdere keren binnen één query moeten definiëren.
• Het is handig als we een alternatief voor een weergave in de database moeten maken.
• Het is handig als we dezelfde berekening meerdere keren tegelijkertijd moeten uitvoeren op meerdere querycomponenten.
• Het is handig als we rangschikkingsfuncties zoals ROW_NUMBER(), RANK() en NTILE() moeten gebruiken.

Enkele van de voordelen worden hieronder gegeven:

gebruik van besturingssysteem

• CTE maakt het onderhoud van de code eenvoudiger.
• CTE vergroot de leesbaarheid van de code.
• Het verhoogt de prestaties van de query.
• CTE maakt het mogelijk om recursieve queries eenvoudig te implementeren.

Soorten CTE in SQL Server

SQL Server verdeelt de CTE (Common Table Expressions) in twee brede categorieën:

1. Recursieve CTE
2. Niet-recursieve CTE

Recursieve CTE

Een veel voorkomende tabelexpressie staat bekend als recursieve CTE en verwijst naar zichzelf. Het concept is gebaseerd op recursie, die wordt gedefinieerd als ' het herhaaldelijk toepassen van een recursief proces of definitie .' Wanneer we een recursieve query uitvoeren, itereert deze herhaaldelijk over een subset van de gegevens. Het wordt eenvoudigweg gedefinieerd als een query die zichzelf aanroept. Er is op een gegeven moment een eindvoorwaarde, dus deze noemt zichzelf niet oneindig.

Een recursieve CTE moet a hebben UNIE ALLEMAAL statement en een tweede querydefinitie die verwijst naar de CTE zelf om recursief te zijn.

Voorbeeld

Laten we begrijpen hoe recursieve CTE werkt in SQL Server. Beschouw de onderstaande verklaring, die genereert een reeks van de eerste vijf oneven getallen:

 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>

Deze CTE geeft de volgende uitvoer waarin we de hiërarchie van werknemersgegevens kunnen zien:

Niet-recursieve CTE

Een algemene tabelexpressie die niet naar zichzelf verwijst, staat bekend als een niet-recursieve CTE. Een niet-recursieve CTE is eenvoudig en gemakkelijker te begrijpen omdat er geen gebruik wordt gemaakt van het concept van recursie. Volgens de CTE-syntaxis begint elke CTE-query met een ' Met '-clausule gevolgd door de CTE-naam en de kolomlijst, en vervolgens AS tussen haakjes.

Nadelen van CTE

Hieronder volgen de beperkingen van het gebruik van CTE in SQL Server:

• CTE-leden kunnen de trefwoordclausules zoals Distinct, Group By,Hating, Top, Joins, etc. niet gebruiken.
• Er kan slechts één keer naar de CTE worden verwezen door het recursieve lid.
• We kunnen de tabelvariabelen en CTE's niet gebruiken als parameters in opgeslagen procedures.
• We weten al dat de CTE kan worden gebruikt in plaats van een weergave, maar een CTE kan niet worden genest, terwijl weergaven dat wel kunnen.
• Omdat het slechts een snelkoppeling is voor een query of subquery, kan deze niet opnieuw worden gebruikt in een andere query.
• Het aantal kolommen in de CTE-argumenten en het aantal kolommen in de query moeten hetzelfde zijn.