TechCodeview

Na uľahčenie zložitých spojení a poddotazov použijeme bežné tabuľkové výrazy alebo CTE servera SQL Server. Poskytuje tiež spôsob dotazovania na hierarchické údaje, ako je napríklad organizačná hierarchia. Tento článok poskytuje úplný prehľad CTE, typy CTE, výhody, nevýhody a ako ich používať v SQL Server.

Čo je CTE na serveri SQL Server?

CTE (Common Table Expression) je množina jednorazových výsledkov, ktorá existuje len počas trvania dotazu . Umožňuje nám odkazovať na údaje v rámci jedného rozsahu vykonania príkazu SELECT, INSERT, UPDATE, DELETE, CREATE VIEW alebo MERGE. Je dočasný, pretože jeho výsledok nemožno nikde uložiť a stratí sa hneď po dokončení vykonávania dotazu. Prvýkrát prišiel s verziou SQL Server 2005. DBA vždy uprednostňoval použitie CTE ako alternatívu k poddotazu/zobrazeniu. Dodržiavajú štandard ANSI SQL 99 a sú v súlade s SQL.

Syntax CTE na serveri SQL Server

Syntax CTE obsahuje názov CTE, voliteľný zoznam stĺpcov a príkaz/dotaz, ktorý definuje spoločný tabuľkový výraz (CTE). Po definovaní CTE ho môžeme použiť ako pohľad v dotazoch SELECT, INSERT, UPDATE, DELETE a MERGE.

Nasleduje základná syntax CTE v SQL Server:

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

V tejto syntaxi:

• Najprv sme zadali názov CTE, na ktorý sa bude neskôr odkazovať v dotaze.
• Ďalším krokom je vytvorenie zoznamu stĺpcov oddelených čiarkami. Zabezpečuje, že počet stĺpcov v argumentoch definície CTE a počet stĺpcov v dotaze musia byť rovnaké. Ak sme nedefinovali stĺpce argumentov CTE, použije sa stĺpce dotazu, ktoré definujú CTE.
• Potom použijeme kľúčové slovo AS za názvom výrazu a potom zadefinujeme príkaz SELECT, ktorého množina výsledkov vyplní CTE.
• Nakoniec použijeme názov CTE v dotaze, ako je príkaz SELECT, INSERT, UPDATE, DELETE a MERGE.

Pri písaní definície dotazu CTE by ste to mali mať na pamäti; nemôžeme použiť nasledujúce vety:

1. ORDER BY, pokiaľ nepoužijete aj klauzulu TOP
2. DO
3. Klauzula OPTION s tipmi na dopyt
4. NA PREHĽADÁVANIE

Obrázok nižšie predstavuje definíciu dotazu CTE.

Tu je prvou časťou výraz CTE, ktorý obsahuje dotaz SQL, ktorý je možné spustiť nezávisle v SQL. A druhá časť je dotaz, ktorý používa CTE na zobrazenie výsledku.

Príklad

Poďme pochopiť, ako funguje CTE v SQL Server pomocou rôznych príkladov. Tu použijeme tabuľku ' zákazníka “ na ukážku. Predpokladajme, že táto tabuľka obsahuje nasledujúce údaje:

V tomto príklade je názov CTE customers_in_newyork , poddotaz, ktorý definuje CTE, vráti tri stĺpce meno zákazníka, email, a štát . Výsledkom je, že CTE customers_in_newyork vráti všetkých zákazníkov, ktorí žijú v štáte New York.

Po definovaní CTE customers_in_newyork sme naň odkázali v SELECT výpis, aby ste získali podrobnosti o tých zákazníkoch, ktorí sa nachádzajú v New Yorku.

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

Po vykonaní vyššie uvedeného príkazu poskytne nasledujúci výstup. Tu vidíme, že výsledok vráti iba informácie o zákazníkoch, ktorí sa nachádzajú v štáte New York.

Viacnásobné CTE

V niektorých prípadoch budeme musieť vytvoriť viacero dopytov CTE a spojiť ich, aby sme videli výsledky. V tomto scenári môžeme použiť koncept viacerých CTE. Na vytvorenie viacerých CTE dopytov a ich zlúčenie do jedného príkazu musíme použiť operátor čiarky. Pred čiarkou musí byť uvedený názov CTE, aby sa rozlíšilo viacero CTE.

Viaceré CTE nám pomáhajú pri zjednodušovaní zložitých dopytov, ktoré sú nakoniec spojené. Každý komplexný kus mal svoj vlastný CTE, na ktorý sa potom dalo odkazovať a spájať mimo klauzuly WITH.

POZNÁMKA: Definíciu viacerých CTE možno definovať pomocou UNION, UNION ALL, JOIN, INTERSECT alebo EXCEPT.

Nižšie uvedená syntax to vysvetľuje jasnejšie:

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

Príklad

Poďme pochopiť, ako funguje viacero CTE na serveri SQL Server. Tu použijeme vyššie uvedené „ zákazníka ' tabuľka na ukážku.

V tomto príklade sme definovali dva názvy CTE customers_in_newyork a customers_in_california . Potom výsledná množina poddotazov týchto CTE vyplní CTE. Nakoniec použijeme názvy CTE v dopyte, ktorý vráti všetkých zákazníkov, ktorí sa nachádzajú v New York a štát Kalifornia .

 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 a štát Kalifornia.

Prečo potrebujeme CTE?

Podobne ako databázové zobrazenia a odvodené tabuľky, CTE môžu uľahčiť písanie a správu zložitých dotazov tým, že sú čitateľnejšie a jednoduchšie. Túto vlastnosť môžeme dosiahnuť rozdelením zložitých dotazov na jednoduché bloky, ktoré je možné opätovne použiť pri prepisovaní dotazu.

Niektoré z jeho prípadov použitia sú uvedené nižšie:

• Je to užitočné, keď potrebujeme definovať odvodenú tabuľku viackrát v rámci jedného dotazu.
• Je to užitočné, keď potrebujeme vytvoriť alternatívu k pohľadu v databáze.
• Je to užitočné, keď potrebujeme vykonať rovnaký výpočet viackrát na viacerých komponentoch dotazu súčasne.
• Je to užitočné, keď potrebujeme použiť hodnotiace funkcie ako ROW_NUMBER(), RANK() a NTILE().

Niektoré z jeho výhod sú uvedené nižšie:

j e s t

• CTE uľahčuje údržbu kódu.
• CTE zvyšuje čitateľnosť kódu.
• Zvyšuje výkon dotazu.
• CTE umožňuje jednoducho implementovať rekurzívne dotazy.

Typy CTE v SQL Server

SQL Server rozdeľuje CTE (Common Table Expressions) do dvoch širokých kategórií:

1. Rekurzívne CTE
2. Nerekurzívne CTE

Rekurzívne CTE

Bežný tabuľkový výraz je známy ako rekurzívny CTE, ktorý odkazuje sám na seba. Jeho koncept je založený na rekurzii, ktorá je definovaná ako „ opakovaná aplikácia rekurzívneho procesu alebo definície .' Keď vykonáme rekurzívny dotaz, opakovane sa iteruje cez podmnožinu údajov. Jednoducho sa definuje ako dopyt, ktorý volá sám seba. V určitom bode existuje koncová podmienka, takže sa nevolá donekonečna.

Rekurzívny CTE musí mať a UNION VŠETKO a druhá definícia dotazu, ktorá odkazuje na samotný CTE, aby bola rekurzívna.

Príklad

Poďme pochopiť, ako funguje rekurzívne CTE na serveri SQL Server. Zvážte nižšie uvedené vyhlásenie, ktoré vygeneruje sériu prvých piatich nepárnych čísel:

 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>

Tento CTE poskytne nasledujúci výstup, kde môžeme vidieť hierarchiu údajov o zamestnancoch:

Nerekurzívne CTE

Bežný tabuľkový výraz, ktorý na seba neodkazuje, je známy ako nerekurzívny CTE. Nerekurzívny CTE je jednoduchý a ľahšie pochopiteľný, pretože nepoužíva koncept rekurzie. Podľa syntaxe CTE bude každý dotaz CTE začínať znakom ' s klauzula ', za ktorou nasleduje názov CTE a zoznam stĺpcov, potom AS so zátvorkami.

Nevýhody CTE

Nasledujú obmedzenia používania CTE na serveri SQL Server:

• Členovia CTE nemôžu používať kľúčové slová ako Distinct, Group By, Have, Top, Joins atď.
• Rekurzívny člen môže odkazovať na CTE iba raz.
• Nemôžeme použiť premenné tabuľky a CTE ako parametre v uložených procedúrach.
• Už vieme, že CTE možno použiť namiesto zobrazenia, ale CTE nemožno vnoriť, zatiaľ čo zobrazenia áno.
• Keďže ide len o skratku pre dotaz alebo poddotaz, nemožno ho znova použiť v inom dotaze.
• Počet stĺpcov v argumentoch CTE a počet stĺpcov v dotaze musí byť rovnaký.