TechCodeview

Vom folosi expresiile comune de tabel sau CTE-urile SQL Server pentru a face mai ușoare îmbinările și subinterogările complexe. De asemenea, oferă o modalitate de a interoga datele ierarhice, cum ar fi o ierarhie organizațională. Acest articol oferă o imagine de ansamblu completă a CTE, tipuri de CTE, avantaje, dezavantaje și cum să le folosiți în SQL Server.

Ce este CTE în SQL Server?

Un CTE (Common Table Expression) este un set unic de rezultate care există doar pe durata interogării . Ne permite să ne referim la date din domeniul de execuție al unei singure instrucțiuni SELECT, INSERT, UPDATE, DELETE, CREATE VIEW sau MERGE. Este temporară deoarece rezultatul său nu poate fi stocat nicăieri și se va pierde de îndată ce execuția unei interogări este finalizată. A venit pentru prima dată cu versiunea SQL Server 2005. Un DBA a preferat întotdeauna ca CTE să fie utilizat ca alternativă la o Subinterogare/Vizualizare. Acestea urmează standardul ANSI SQL 99 și sunt compatibile cu SQL.

Sintaxa CTE în SQL Server

Sintaxa CTE include un nume CTE, o listă de coloane opțională și o instrucțiune/interogare care definește expresia tabelului comun (CTE). După definirea CTE, îl putem folosi ca vizualizare într-o interogare SELECT, INSERT, UPDATE, DELETE și MERGE.

Următoarea este sintaxa de bază a CTE în SQL Server:

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

În această sintaxă:

• Am specificat mai întâi numele CTE la care se va face referire mai târziu într-o interogare.
• Următorul pas este crearea unei liste de coloane separate prin virgulă. Se asigură că numărul de coloane din argumentele definiției CTE și numărul de coloane din interogare trebuie să fie același. Dacă nu am definit coloanele argumentelor CTE, va folosi coloanele de interogare care definesc CTE.
• După aceea, vom folosi cuvântul cheie AS după numele expresiei și apoi vom defini o instrucțiune SELECT al cărei set de rezultate populează CTE.
• În cele din urmă, vom folosi numele CTE într-o interogare precum instrucțiunea SELECT, INSERT, UPDATE, DELETE și MERGE.

Ar trebui să țineți cont atunci când scrieți definiția interogării CTE; nu putem folosi următoarele clauze:

1. ORDER BY, cu excepția cazului în care utilizați și ca clauză TOP
2. ÎN
3. Clauza OPTION cu indicii de interogare
4. PENTRU RAUȚIȚI

Imaginea de mai jos este reprezentarea definiției interogării CTE.

Aici, prima parte este o expresie CTE care conține o interogare SQL care poate fi rulată independent în SQL. Și a doua parte este interogarea care utilizează CTE pentru a afișa rezultatul.

Exemplu

Să înțelegem cum funcționează CTE în SQL Server folosind diverse exemple. Aici, vom folosi un tabel ' client ' pentru o demonstrație. Să presupunem că acest tabel conține următoarele date:

În acest exemplu, numele CTE este customers_in_newyork , subinterogarea care definește CTE returnează cele trei coloane numele clientului, e-mail, și stat . Drept urmare, CTE customers_in_newyork va returna toți clienții care locuiesc în statul New York.

După definirea clienților CTE_in_newyork, l-am referit în SELECTAȚI declarație pentru a obține detaliile acelor clienți care se află în New York.

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

După executarea instrucțiunii de mai sus, va da următoarea ieșire. Aici, putem vedea că rezultatul returnează doar acele informații despre clienți care se află în statul New York.

CTE multiple

În unele cazuri, va trebui să creăm mai multe interogări CTE și să le unim împreună pentru a vedea rezultatele. Putem folosi conceptul de CTE-uri multiple în acest scenariu. Trebuie să folosim operatorul virgulă pentru a crea mai multe interogări CTE și pentru a le îmbina într-o singură declarație. Operatorul virgulă „,” trebuie să fie precedat de numele CTE pentru a distinge mai multe CTE.

CTE-urile multiple ne ajută să simplificăm interogările complexe care sunt în cele din urmă unite împreună. Fiecare piesă complexă avea propriul CTE, care putea fi apoi referit și îmbinat în afara clauzei WITH.

NOTĂ: Definiția CTE multiplă poate fi definită folosind UNION, UNION ALL, JOIN, INTERSECT sau EXCEPT.

Sintaxa de mai jos o explică mai clar:

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

Exemplu

Să înțelegem cum funcționează mai multe CTE în SQL Server. Aici, vom folosi cele de mai sus client masa pentru o demonstrație.

În acest exemplu, am definit cele două nume CTE customers_in_newyork și customers_in_california . Apoi, setul de rezultate de subinterogări ale acestor CTE-uri populează CTE. În cele din urmă, vom folosi numele CTE într-o interogare care va returna toți clienții care se află în New York și Statul California .

 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 și statul California.

De ce avem nevoie de CTE?

La fel ca vizualizările bazei de date și tabelele derivate, CTE-urile pot facilita scrierea și gestionarea interogărilor complexe, făcându-le mai lizibile și mai simple. Putem realiza această caracteristică prin descompunerea interogărilor complexe în blocuri simple care pot fi reutilizate în rescrierea interogării.

Unele dintre cazurile sale de utilizare sunt prezentate mai jos:

• Este util atunci când trebuie să definim un tabel derivat de mai multe ori într-o singură interogare.
• Este util atunci când trebuie să creăm o alternativă la o vizualizare în baza de date.
• Este util atunci când trebuie să efectuăm același calcul de mai multe ori pe mai multe componente de interogare simultan.
• Este util atunci când trebuie să folosim funcții de clasare precum ROW_NUMBER(), RANK() și NTILE().

Unele dintre avantajele sale sunt prezentate mai jos:

js settimeout

• CTE facilitează întreținerea codului mai ușor.
• CTE crește lizibilitatea codului.
• Mărește performanța interogării.
• CTE face posibilă implementarea cu ușurință a interogărilor recursive.

Tipuri de CTE în SQL Server

SQL Server împarte CTE (Common Table Expressions) în două categorii mari:

1. CTE recursiv
2. CTE non-recursiv

CTE recursiv

O expresie de tabel comună este cunoscută ca CTE recursiv care se referă la sine. Conceptul său se bazează pe recursivitate, care este definită ca „ aplicarea în mod repetat a unui proces recursiv sau a unei definiții .' Când executăm o interogare recursivă, aceasta repetă în mod repetat peste un subset de date. Este pur și simplu definită ca o interogare care se autoapelează. Există o condiție finală la un moment dat, așa că nu se numește la infinit.

Un CTE recursiv trebuie să aibă un UNIREA TOȚI declarație și o a doua definiție de interogare care face referire la CTE însuși pentru a fi recursiv.

Exemplu

Să înțelegem cum funcționează CTE recursiv în SQL Server. Luați în considerare afirmația de mai jos, care generează o serie de primele cinci numere impare:

 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>

Acest CTE va oferi următoarea ieșire unde putem vedea ierarhia datelor angajaților:

CTE non-recursiv

O expresie obișnuită de tabel care nu se referă la sine este cunoscută ca CTE nerecursivă. Un CTE non-recursiv este simplu și mai ușor de înțeles deoarece nu folosește conceptul de recursivitate. Conform sintaxei CTE, fiecare interogare CTE va începe cu un „ Cu ', urmată de numele CTE și lista de coloane, apoi AS cu paranteză.

Dezavantajele CTE

Următoarele sunt limitările utilizării CTE în SQL Server:

• Membrii CTE nu pot folosi clauzele de cuvinte cheie precum Distinct, Group By, Having, Top, Joins etc.
• CTE poate fi referit o singură dată de către membrul recursiv.
• Nu putem folosi variabilele tabelului și CTE-urile ca parametri în procedurile stocate.
• Știm deja că CTE-ul ar putea fi folosit în locul unei vizualizări, dar un CTE nu poate fi imbricat, în timp ce Views poate.
• Deoarece este doar o comandă rapidă pentru o interogare sau subinterogare, nu poate fi reutilizată într-o altă interogare.
• Numărul de coloane din argumentele CTE și numărul de coloane din interogare trebuie să fie același.