TechCodeview

Vi vil bruge SQL Servers Common Table Expressions eller CTE'er til at gøre komplekse joinforbindelser og underforespørgsler nemmere. Det giver også en måde at forespørge på hierarkiske data, såsom et organisatorisk hierarki. Denne artikel giver et komplet overblik over CTE, typer af CTE, fordele, ulemper og hvordan man bruger dem i SQL Server.

Hvad er CTE i SQL Server?

En CTE (Common Table Expression) er et engangsresultatsæt, der kun eksisterer under forespørgslens varighed . Det giver os mulighed for at henvise til data inden for en enkelt SELECT, INSERT, UPDATE, DELETE, CREATE VIEW eller MERGE-sætnings eksekveringsomfang. Det er midlertidigt, fordi dets resultat ikke kan gemmes nogen steder og vil gå tabt, så snart en forespørgsel er udført. Det kom først med SQL Server 2005-versionen. En DBA foretrak altid CTE at bruge som et alternativ til en underforespørgsel/visning. De følger ANSI SQL 99-standarden og er SQL-kompatible.

CTE-syntaks i SQL Server

CTE-syntaksen inkluderer et CTE-navn, en valgfri kolonneliste og en sætning/forespørgsel, der definerer det fælles tabeludtryk (CTE). Efter at have defineret CTE, kan vi bruge det som en visning i en SELECT, INSERT, UPDATE, DELETE og FLÉNING forespørgsel.

Følgende er den grundlæggende syntaks for CTE i SQL Server:

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

I denne syntaks:

• Vi har først specificeret det CTE-navn, der vil blive henvist til senere i en forespørgsel.
• Det næste trin er at oprette en liste over kommaseparerede kolonner. Det sikrer, at antallet af kolonner i CTE-definitionsargumenterne og antallet af kolonner i forespørgslen skal være det samme. Hvis vi ikke har defineret CTE-argumenternes kolonner, vil den bruge forespørgselskolonnerne, der definerer CTE.
• Derefter vil vi bruge AS-nøgleordet efter udtrykkets navn og derefter definere en SELECT-sætning, hvis resultatsæt udfylder CTE.
• Til sidst vil vi bruge CTE-navnet i en forespørgsel som SELECT, INSERT, UPDATE, DELETE og MERGE-sætning.

Det bør huske på, mens du skriver CTE-forespørgselsdefinitionen; vi kan ikke bruge følgende klausuler:

1. BESTIL BY, medmindre du også bruger som TOP-klausul
2. IND I
3. OPTION-klausul med forespørgselstip
4. TIL Browse

Billedet nedenfor er repræsentationen af ​​CTE-forespørgselsdefinitionen.

Her er den første del et CTE-udtryk, der indeholder en SQL-forespørgsel, der kan køres uafhængigt i SQL. Og den anden del er forespørgslen, der bruger CTE til at vise resultatet.

Eksempel

Lad os forstå, hvordan CTE fungerer i SQL Server ved hjælp af forskellige eksempler. Her skal vi bruge en tabel ' kunde ' til en demonstration. Antag, at denne tabel indeholder følgende data:

I dette eksempel er CTE-navnet kunder_i_newyork , returnerer underforespørgslen, der definerer CTE'en, de tre kolonner kundenavn, e-mail, og stat . Som et resultat vil CTE-kunderne_in_newyork returnere alle kunder, der bor i staten New York.

Efter at have defineret CTE customers_in_newyork, har vi henvist til det i VÆLG erklæring for at få oplysninger om de kunder, der er placeret i New York.

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

Efter at have udført ovenstående sætning, vil den give følgende output. Her kan vi se, at resultatet kun returnerer de kundeoplysninger, der er placeret i staten New York.

Flere CTE

I nogle tilfælde bliver vi nødt til at oprette flere CTE-forespørgsler og samle dem for at se resultaterne. Vi kan bruge flere CTE-koncepter i dette scenarie. Vi skal bruge kommaoperatoren til at oprette flere CTE-forespørgsler og flette dem til en enkelt sætning. Kommaoperatoren ',' skal være foranstillet af CTE-navnet for at skelne mellem flere CTE.

Flere CTE'er hjælper os med at forenkle komplekse forespørgsler, der i sidste ende bliver sat sammen. Hvert kompleks stykke havde sin egen CTE, som derefter kunne refereres til og sammenføjes uden for WITH-klausulen.

BEMÆRK: Den multiple CTE-definition kan defineres ved hjælp af UNION, UNION ALL, JOIN, INTERSECT eller EXCEPT.

Nedenstående syntaks forklarer det mere tydeligt:

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

Eksempel

Lad os forstå, hvordan flere CTE fungerer i SQL Server. Her skal vi bruge ovenstående ' kunde ' bord til en demonstration.

I dette eksempel har vi defineret de to CTE-navne kunder_i_newyork og kunder_i_californien . Derefter udfylder resultatsættet af underforespørgsler af disse CTE'er CTE'en. Til sidst vil vi bruge CTE-navnene i en forespørgsel, der returnerer alle kunder, der befinder sig i New York og staten Californien .

 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 og California State.

Hvorfor har vi brug for CTE?

Ligesom databasevisninger og afledte tabeller kan CTE'er gøre det nemmere at skrive og administrere komplekse forespørgsler ved at gøre dem mere læsbare og enkle. Vi kan opnå denne egenskab ved at nedbryde de komplekse forespørgsler i simple blokke, der kan genbruges ved omskrivning af forespørgslen.

Nogle af dens anvendelsestilfælde er angivet nedenfor:

• Det er nyttigt, når vi skal definere en afledt tabel flere gange inden for en enkelt forespørgsel.
• Det er nyttigt, når vi skal lave et alternativ til en visning i databasen.
• Det er nyttigt, når vi skal udføre den samme beregning flere gange på flere forespørgselskomponenter samtidigt.
• Det er nyttigt, når vi skal bruge rangeringsfunktioner som ROW_NUMBER(), RANK() og NTILE().

Nogle af dens fordele er angivet nedenfor:

forskel mellem firma og virksomhed

• CTE letter kodevedligeholdelse.
• CTE øger kodens læsbarhed.
• Det øger ydelsen af ​​forespørgslen.
• CTE gør det nemt at implementere rekursive forespørgsler.

Typer af CTE i SQL Server

SQL Server opdeler CTE (Common Table Expressions) i to brede kategorier:

1. Rekursiv CTE
2. Ikke-rekursiv CTE

Rekursiv CTE

Et almindeligt tabeludtryk er kendt som rekursiv CTE, der refererer til sig selv. Dens koncept er baseret på rekursion, som er defineret som ' anvendelsen af ​​en rekursiv proces eller definition gentagne gange .' Når vi udfører en rekursiv forespørgsel, itererer den gentagne gange over en delmængde af dataene. Det er simpelthen defineret som en forespørgsel, der kalder sig selv. Der er en slutbetingelse på et tidspunkt, så den kalder sig ikke uendeligt.

En rekursiv CTE skal have en UNION ALLE sætning og en anden forespørgselsdefinition, der refererer til selve CTE'en for at være rekursiv.

Eksempel

Lad os forstå, hvordan rekursiv CTE fungerer i SQL Server. Overvej nedenstående udsagn, som genererer en række af de første fem ulige tal:

 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>

Denne CTE vil give følgende output, hvor vi kan se hierarkiet af medarbejderdata:

Ikke-rekursiv CTE

Et almindeligt tabeludtryk, der ikke refererer til sig selv, er kendt som en ikke-rekursiv CTE. En ikke-rekursiv CTE er enkel og lettere at forstå, fordi den ikke bruger begrebet rekursion. Ifølge CTE-syntaksen vil hver CTE-forespørgsel begynde med et ' Med ' klausul efterfulgt af CTE-navnet og kolonnelisten, derefter AS med parentes.

Ulemper ved CTE

Følgende er begrænsningerne ved at bruge CTE i SQL Server:

• CTE-medlemmer er ikke i stand til at bruge søgeordsbestemmelserne som Distinct, Group By, Have, Top, Joins osv.
• CTE kan kun refereres én gang af det rekursive medlem.
• Vi kan ikke bruge tabelvariablerne og CTE'erne som parametre i lagrede procedurer.
• Vi ved allerede, at CTE kunne bruges i stedet for en visning, men en CTE kan ikke indlejres, mens visninger kan.
• Da det kun er en genvej til en forespørgsel eller underforespørgsel, kan den ikke genbruges i en anden forespørgsel.
• Antallet af kolonner i CTE-argumenterne og antallet af kolonner i forespørgslen skal være det samme.