TechCodeview

Vi kommer att använda SQL Servers vanliga tabelluttryck eller CTE:er för att göra komplexa kopplingar och underfrågor enklare. Det ger också ett sätt att fråga efter hierarkisk data, till exempel en organisatorisk hierarki. Den här artikeln ger en fullständig översikt över CTE, typer av CTE, fördelar, nackdelar och hur man använder dem i SQL Server.

Vad är CTE i SQL Server?

En CTE (Common Table Expression) är en engångsresultatuppsättning som bara existerar under hela frågan . Det tillåter oss att referera till data inom en enda SELECT, INSERT, UPDATE, DELETE, CREATE VIEW eller MERGE-satsens exekveringsomfång. Det är temporärt eftersom dess resultat inte kan lagras någonstans och kommer att gå förlorat så snart en frågas körning är klar. Den kom först med SQL Server 2005-versionen. En DBA föredrog alltid att använda CTE som ett alternativ till en underfråga/vy. De följer ANSI SQL 99-standarden och är SQL-kompatibla.

CTE-syntax i SQL Server

CTE-syntaxen inkluderar ett CTE-namn, en valfri kolumnlista och en sats/fråga som definierar det gemensamma tabelluttrycket (CTE). Efter att ha definierat CTE kan vi använda den som en vy i en SELECT-, INSERT-, UPDATE-, DELETE- och MERGE-fråga.

Följande är den grundläggande syntaxen för CTE i SQL Server:

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

I denna syntax:

• Vi har först specificerat CTE-namnet som kommer att hänvisas till senare i en fråga.
• Nästa steg är att skapa en lista med kommaseparerade kolumner. Det säkerställer att antalet kolumner i CTE-definitionsargumenten och antalet kolumner i frågan måste vara detsamma. Om vi ​​inte har definierat CTE-argumentens kolumner kommer den att använda frågekolumnerna som definierar CTE.
• Därefter använder vi nyckelordet AS efter uttryckets namn och definierar sedan en SELECT-sats vars resultatuppsättning fyller CTE.
• Slutligen kommer vi att använda CTE-namnet i en fråga som SELECT, INSERT, UPDATE, DELETE och MERGE-satsen.

Det bör ha i åtanke när du skriver CTE-frågedefinitionen; vi kan inte använda följande klausuler:

1. BESTÄLL BY om du inte också använder som TOP-klausul
2. IN I
3. OPTION-klausul med frågetips
4. FÖR BLODDNING

Bilden nedan är representationen av CTE-frågans definition.

Här är den första delen ett CTE-uttryck som innehåller en SQL-fråga som kan köras oberoende i SQL. Och den andra delen är frågan som använder CTE för att visa resultatet.

Exempel

Låt oss förstå hur CTE fungerar i SQL Server med hjälp av olika exempel. Här kommer vi att använda en tabell ' kund ' för en demonstration. Anta att denna tabell innehåller följande data:

I det här exemplet är CTE-namnet kunder_i_newyork , returnerar underfrågan som definierar CTE de tre kolumnerna kundnamn, e-post, och stat . Som ett resultat kommer CTE customers_in_newyork att returnera alla kunder som bor i delstaten New York.

Efter att ha definierat CTE customers_in_newyork har vi hänvisat till det i VÄLJ uttalande för att få information om de kunder som finns 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 att ha kört ovanstående sats kommer den att ge följande utdata. Här kan vi se att resultatet endast returnerar den kundinformation som finns i delstaten New York.

Flera CTE

I vissa fall måste vi skapa flera CTE-frågor och sammanfoga dem för att se resultaten. Vi kan använda flera CTE-koncept i detta scenario. Vi måste använda komma-operatorn för att skapa flera CTE-frågor och slå samman dem till ett enda uttalande. Kommaoperatorn ',' måste föregås av CTE-namnet för att särskilja flera CTE.

Flera CTE:er hjälper oss att förenkla komplexa frågor som så småningom sammanfogas. Varje komplex del hade sin egen CTE, som sedan kunde refereras till och sammanfogas utanför WITH-satsen.

OBS: Definitionen av flera CTE kan definieras med UNION, UNION ALL, JOIN, INTERSECT eller EXCEPT.

Syntaxen nedan förklarar det tydligare:

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

Exempel

Låt oss förstå hur flera CTE fungerar i SQL Server. Här kommer vi att använda ovanstående ' kund ' bord för en demonstration.

I det här exemplet har vi definierat de två CTE-namnen kunder_i_newyork och kunder_i_kalifornien . Sedan fyller resultatuppsättningen av underfrågor för dessa CTE:er i CTE. Slutligen kommer vi att använda CTE-namnen i en fråga som kommer att returnera alla kunder som finns i New York och delstaten Kalifornien .

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

Varför behöver vi CTE?

Precis som databasvyer och härledda tabeller kan CTE:er göra det lättare att skriva och hantera komplexa frågor genom att göra dem mer läsbara och enkla. Vi kan uppnå denna egenskap genom att dela upp de komplexa frågorna i enkla block som kan återanvändas vid omskrivning av frågan.

Några av dess användningsfall ges nedan:

• Det är användbart när vi behöver definiera en härledd tabell flera gånger inom en enda fråga.
• Det är användbart när vi behöver skapa ett alternativ till en vy i databasen.
• Det är användbart när vi behöver utföra samma beräkning flera gånger på flera frågekomponenter samtidigt.
• Det är användbart när vi behöver använda rankningsfunktioner som ROW_NUMBER(), RANK() och NTILE().

Några av dess fördelar ges nedan:

blockera annonser på youtube android

• CTE underlättar kodunderhåll enklare.
• CTE ökar kodens läsbarhet.
• Det ökar prestandan för frågan.
• CTE gör det möjligt att enkelt implementera rekursiva frågor.

Typer av CTE i SQL Server

SQL Server delar in CTE (Common Table Expressions) i två breda kategorier:

1. Rekursiv CTE
2. Icke-rekursiv CTE

Rekursiv CTE

Ett vanligt tabelluttryck är känt som rekursiv CTE som refererar till sig själv. Dess koncept är baserat på rekursion, vilket definieras som ' tillämpningen av en rekursiv process eller definition upprepade gånger .' När vi kör en rekursiv fråga, itererar den upprepade gånger över en delmängd av data. Det definieras helt enkelt som en fråga som anropar sig själv. Det finns ett slutvillkor någon gång, så det kallar sig inte oändligt.

En rekursiv CTE måste ha en UNION ALLA uttalande och en andra frågedefinition som refererar till själva CTE för att vara rekursiv.

Exempel

Låt oss förstå hur rekursiv CTE fungerar i SQL Server. Betrakta nedanstående uttalande, som genererar en serie av de första fem udda talen:

 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>

Denna CTE kommer att ge följande utdata där vi kan se hierarkin av personaldata:

Icke-rekursiv CTE

Ett vanligt tabelluttryck som inte refererar till sig självt kallas en icke-rekursiv CTE. En icke-rekursiv CTE är enkel och lättare att förstå eftersom den inte använder begreppet rekursion. Enligt CTE-syntaxen börjar varje CTE-fråga med ett ' Med ' klausul följt av CTE-namnet och kolumnlistan, sedan AS med parentes.

Nackdelar med CTE

Följande är begränsningarna för att använda CTE i SQL Server:

• CTE-medlemmar kan inte använda nyckelordsklausulerna som Distinct, Group By, Have, Top, Joins, etc.
• CTE kan endast refereras en gång av den rekursiva medlemmen.
• Vi kan inte använda tabellvariablerna och CTE som parametrar i lagrade procedurer.
• Vi vet redan att CTE kan användas i stället för en vy, men en CTE kan inte kapslas, medan Views kan.
• Eftersom det bara är en genväg för en fråga eller underfråga, kan den inte återanvändas i en annan fråga.
• Antalet kolumner i CTE-argumenten och antalet kolumner i frågan måste vara detsamma.