TechCodeview

Vi vil bruke SQL Servers vanlige tabelluttrykk eller CTE-er for å gjøre komplekse sammenføyninger og underspørringer enklere. Det gir også en måte å søke etter hierarkiske data, for eksempel et organisasjonshierarki. Denne artikkelen gir en fullstendig oversikt over CTE, typer CTE, fordeler, ulemper og hvordan du bruker dem i SQL Server.

Hva er CTE i SQL Server?

En CTE (Common Table Expression) er et engangsresultatsett som bare eksisterer under varigheten av spørringen . Den lar oss referere til data innenfor en enkelt SELECT-, INSERT-, UPDATE-, DELETE-, CREATE VIEW- eller MERGE-setnings utførelsesomfang. Det er midlertidig fordi resultatet ikke kan lagres hvor som helst og vil gå tapt så snart en spørring er fullført. Den kom først med SQL Server 2005-versjonen. En DBA foretrukket alltid CTE å bruke som et alternativ til en underspørring/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 setning/spørring som definerer det vanlige tabelluttrykket (CTE). Etter å ha definert CTE, kan vi bruke den som en visning i en SELECT-, INSERT-, UPDATE-, DELETE- og MERGE-spørring.

Følgende er den grunnleggende syntaksen til CTE i SQL Server:

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

I denne syntaksen:

• Vi har først spesifisert CTE-navnet som vil bli referert til senere i en spørring.
• Det neste trinnet er å lage en liste over kommadelte kolonner. Det sikrer at antall kolonner i CTE-definisjonsargumentene og antall kolonner i spørringen må være det samme. Hvis vi ikke har definert CTE-argumentenes kolonner, vil den bruke spørringskolonnene som definerer CTE.
• Etter det bruker vi AS-nøkkelordet etter uttrykksnavnet og definerer deretter en SELECT-setning hvis resultatsett fyller ut CTE.
• Til slutt vil vi bruke CTE-navnet i en spørring som SELECT, INSERT, UPDATE, DELETE og MERGE-setning.

Det bør huskes når du skriver CTE-søkedefinisjonen; vi kan ikke bruke følgende klausuler:

1. BESTILL BY med mindre du også bruker som TOP-klausul
2. INN I
3. OPTION-klausul med spørringstips
4. FOR Browse

Bildet nedenfor er representasjonen av CTE-søkedefinisjonen.

Her er den første delen et CTE-uttrykk som inneholder en SQL-spørring som kan kjøres uavhengig i SQL. Og den andre delen er spørringen som bruker CTE for å vise resultatet.

Eksempel

La oss forstå hvordan CTE fungerer i SQL Server ved å bruke forskjellige eksempler. Her skal vi bruke en tabell ' kunde ' for en demonstrasjon. Anta at denne tabellen inneholder følgende data:

I dette eksemplet er CTE-navnet kunder_i_newyork , returnerer underspørringen som definerer CTE de tre kolonnene kundenavn, e-post, og stat . Som et resultat vil CTE customers_in_newyork returnere alle kunder som bor i New York State.

Etter å ha definert CTE customers_in_newyork, har vi referert til det i PLUKKE UT uttalelse for å få detaljene til de kundene som befinner seg i New York.

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

Etter å ha utført setningen ovenfor, vil den gi følgende utgang. Her kan vi se at resultatet bare returnerer den kundeinformasjonen som befinner seg i staten New York.

Flere CTE

I noen tilfeller må vi opprette flere CTE-spørringer og slå dem sammen for å se resultatene. Vi kan bruke flere CTE-konsepter i dette scenariet. Vi må bruke komma-operatoren til å lage flere CTE-spørringer og slå dem sammen til en enkelt setning. Kommaoperatoren ',' må innledes med CTE-navnet for å skille flere CTE.

Flere CTEer hjelper oss med å forenkle komplekse spørringer som til slutt slås sammen. Hvert komplekse stykke hadde sin egen CTE, som deretter kunne refereres til og slås sammen utenfor WITH-klausulen.

MERK: Multiple CTE-definisjonen kan defineres med UNION, UNION ALL, JOIN, INTERSECT eller EXCEPT.

Syntaksen nedenfor forklarer det tydeligere:

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

Eksempel

La oss forstå hvordan flere CTE fungerer i SQL Server. Her skal vi bruke ovennevnte ' kunde ' bord for en demonstrasjon.

I dette eksemplet har vi definert de to CTE-navnene kunder_i_newyork og kunder_i_california . Deretter fyller resultatsettet med underspørringer til disse CTE-ene CTE-en. Til slutt vil vi bruke CTE-navnene i en spørring som vil returnere alle kunder som befinner seg i New York og California delstat .

 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 trenger vi CTE?

I likhet med databasevisninger og avledede tabeller, kan CTE-er gjøre det enklere å skrive og administrere komplekse spørringer ved å gjøre dem mer lesbare og enkle. Vi kan oppnå denne egenskapen ved å bryte ned de komplekse spørringene i enkle blokker som kan gjenbrukes ved omskrivning av spørringen.

Noen av brukstilfellene er gitt nedenfor:

• Det er nyttig når vi trenger å definere en avledet tabell flere ganger innenfor en enkelt spørring.
• Det er nyttig når vi skal lage et alternativ til en visning i databasen.
• Det er nyttig når vi trenger å utføre den samme beregningen flere ganger på flere spørringskomponenter samtidig.
• Det er nyttig når vi trenger å bruke rangeringsfunksjoner som ROW_NUMBER(), RANK() og NTILE().

Noen av fordelene er gitt nedenfor:

java fargekoder

• CTE forenkler kodevedlikehold enklere.
• CTE øker kodens lesbarhet.
• Det øker ytelsen til spørringen.
• CTE gjør det mulig å implementere rekursive spørringer enkelt.

Typer CTE i SQL Server

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

1. Rekursiv CTE
2. Ikke-rekursiv CTE

Rekursiv CTE

Et vanlig tabelluttrykk er kjent som rekursiv CTE som refererer til seg selv. Konseptet er basert på rekursjon, som er definert som ' bruk av en rekursiv prosess eller definisjon gjentatte ganger .' Når vi utfører en rekursiv spørring, itererer den gjentatte ganger over en delmengde av dataene. Det er ganske enkelt definert som en spørring som kaller seg selv. Det er en sluttbetingelse på et tidspunkt, så den kaller seg ikke uendelig.

En rekursiv CTE må ha en UNION ALLE setning og en andre spørringsdefinisjon som refererer til selve CTE for å være rekursiv.

Eksempel

La oss forstå hvordan rekursiv CTE fungerer i SQL Server. Tenk på utsagnet nedenfor, som genererer en serie med de fem første oddetallene:

 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 gi følgende utgang der vi kan se hierarkiet av ansattes data:

Ikke-rekursiv CTE

Et vanlig tabelluttrykk som ikke refererer til seg selv er kjent som en ikke-rekursiv CTE. En ikke-rekursiv CTE er enkel og lettere å forstå fordi den ikke bruker begrepet rekursjon. I henhold til CTE-syntaksen vil hver CTE-spørring begynne med en ' Med ' klausul etterfulgt av CTE-navnet og kolonnelisten, deretter AS med parentes.

Ulemper med CTE

Følgende er begrensningene ved bruk av CTE i SQL Server:

• CTE-medlemmer kan ikke bruke nøkkelordklausulene som Distinct, Group By, Have, Top, Joins, etc.
• CTE kan kun refereres én gang av det rekursive medlemmet.
• Vi kan ikke bruke tabellvariablene og CTE-ene som parametere i lagrede prosedyrer.
• Vi vet allerede at CTE kan brukes i stedet for en visning, men en CTE kan ikke nestes, mens visninger kan.
• Siden det bare er en snarvei for en spørring eller underspørring, kan den ikke gjenbrukes i en annen spørring.
• Antall kolonner i CTE-argumentene og antall kolonner i spørringen må være det samme.