TechCodeview

K usnadnění složitých spojení a poddotazů použijeme běžné tabulkové výrazy nebo CTE serveru SQL Server. Poskytuje také způsob dotazování na hierarchická data, jako je organizační hierarchie. Tento článek poskytuje úplný přehled CTE, typy CTE, výhody, nevýhody a jak je používat v SQL Server.

Co je CTE v SQL Server?

CTE (Common Table Expression) je jednorázová sada výsledků, která existuje pouze po dobu trvání dotazu . Umožňuje nám odkazovat na data v rámci rozsahu provádění příkazů SELECT, INSERT, UPDATE, DELETE, CREATE VIEW nebo MERGE. Je dočasný, protože jeho výsledek nelze nikam uložit a bude ztracen, jakmile se dokončí provádění dotazu. Poprvé přišel s verzí SQL Server 2005. DBA vždy preferoval použití CTE jako alternativu k dílčímu dotazu/zobrazení. Dodržují standard ANSI SQL 99 a jsou kompatibilní s SQL.

Syntaxe CTE v SQL Server

Syntaxe CTE zahrnuje název CTE, volitelný seznam sloupců a příkaz/dotaz, který definuje společný tabulkový výraz (CTE). Po definování CTE jej můžeme použít jako pohled v dotazech SELECT, INSERT, UPDATE, DELETE a MERGE.

Níže je uvedena základní syntaxe CTE v SQL Server:

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

V této syntaxi:

• Nejprve jsme zadali název CTE, na který bude později v dotazu odkazováno.
• Dalším krokem je vytvoření seznamu sloupců oddělených čárkami. Zajišťuje, že počet sloupců v argumentech definice CTE a počet sloupců v dotazu musí být stejný. Pokud jsme nedefinovali sloupce argumentů CTE, použije se sloupce dotazu, které definují CTE.
• Poté použijeme klíčové slovo AS za názvem výrazu a poté definujeme příkaz SELECT, jehož sada výsledků naplní CTE.
• Nakonec použijeme název CTE v dotazu, jako je příkaz SELECT, INSERT, UPDATE, DELETE a MERGE.

Měli byste to mít na paměti při psaní definice dotazu CTE; nemůžeme použít následující věty:

1. ORDER BY, pokud nepoužijete také jako klauzuli TOP
2. DO
3. Klauzule OPTION s radami pro dotazy
4. PRO PROCHÁZENÍ

Níže uvedený obrázek představuje definici dotazu CTE.

Zde je první částí výraz CTE, který obsahuje dotaz SQL, který lze v SQL spustit nezávisle. A druhá část je dotaz, který používá CTE k zobrazení výsledku.

Příklad

Pojďme pochopit, jak CTE funguje v SQL Server pomocí různých příkladů. Zde použijeme tabulku ' zákazník “ na ukázku. Předpokládejme, že tato tabulka obsahuje následující údaje:

V tomto příkladu je název CTE zákazníci_v_newyorku , poddotaz, který definuje CTE, vrátí tři sloupce jméno zákazníka, email, a Stát . Výsledkem je, že CTE customers_in_newyork vrátí všechny zákazníky, kteří žijí ve státě New York.

Po definování CTE customers_in_newyork jsme na něj odkazovali v VYBRAT prohlášení, abyste získali podrobnosti o těch zákaznících, kteří se nacházejí 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 provedení výše uvedeného příkazu poskytne následující výstup. Zde vidíme, že výsledek vrací pouze ty informace o zákaznících, kteří se nacházejí ve státě New York.

Více CTE

V některých případech budeme muset vytvořit více CTE dotazů a spojit je dohromady, abychom viděli výsledky. V tomto scénáři můžeme použít koncept více CTE. K vytvoření více CTE dotazů a jejich sloučení do jednoho příkazu musíme použít operátor čárka. Před čárkou musí být uveden název CTE, aby bylo možné rozlišit více CTE.

Více CTE nám pomáhá zjednodušit složité dotazy, které jsou nakonec spojeny dohromady. Každý složitý kus měl svůj vlastní CTE, na který bylo možné odkazovat a připojit je mimo klauzuli WITH.

POZNÁMKA: Vícenásobnou definici CTE lze definovat pomocí UNION, UNION ALL, JOIN, INTERSECT nebo EXCEPT.

Níže uvedená syntaxe to vysvětluje jasněji:

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

Příklad

Pojďme pochopit, jak funguje více CTE na serveru SQL Server. Zde použijeme výše uvedené ' zákazník ' stůl pro ukázku.

V tomto příkladu jsme definovali dva názvy CTE zákazníci_v_newyorku a customers_in_california . Potom výsledná sada dílčích dotazů těchto CTE naplní CTE. Nakonec použijeme názvy CTE v dotazu, který vrátí všechny zákazníky, kteří se nacházejí v New York a stát Kalifornie .

 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 stát Kalifornie.

Proč potřebujeme CTE?

Podobně jako databázové pohledy a odvozené tabulky mohou CTE usnadnit psaní a správu složitých dotazů tím, že jsou čitelnější a jednodušší. Této vlastnosti můžeme dosáhnout rozdělením složitých dotazů do jednoduchých bloků, které lze znovu použít při přepisování dotazu.

Některé z jeho případů použití jsou uvedeny níže:

• Je to užitečné, když potřebujeme definovat odvozenou tabulku vícekrát v rámci jednoho dotazu.
• Je to užitečné, když potřebujeme vytvořit alternativu k pohledu v databázi.
• Je to užitečné, když potřebujeme provést stejný výpočet vícekrát na více komponentách dotazu současně.
• Je to užitečné, když potřebujeme použít hodnotící funkce jako ROW_NUMBER(), RANK() a NTILE().

Některé z jeho výhod jsou uvedeny níže:

příkaz git push

• CTE usnadňuje údržbu kódu.
• CTE zvyšuje čitelnost kódu.
• Zvyšuje výkon dotazu.
• CTE umožňuje snadno implementovat rekurzivní dotazy.

Typy CTE v SQL Server

SQL Server rozděluje CTE (Common Table Expressions) do dvou širokých kategorií:

1. Rekurzivní CTE
2. Nerekurzivní CTE

Rekurzivní CTE

Běžný tabulkový výraz je známý jako rekurzivní CTE, který odkazuje sám na sebe. Jeho koncept je založen na rekurzi, která je definována jako „ opakované použití rekurzivního procesu nebo definice .' Když provádíme rekurzivní dotaz, opakovaně iteruje přes podmnožinu dat. Jednoduše se definuje jako dotaz, který volá sám sebe. V určitém bodě existuje koncová podmínka, takže se nevolá do nekonečna.

Rekurzivní CTE musí mít a UNION VŠECHNY a druhá definice dotazu, která odkazuje na samotný CTE, aby byla rekurzivní.

Příklad

Pojďme pochopit, jak funguje rekurzivní CTE v SQL Server. Zvažte níže uvedené prohlášení, které vygeneruje řadu prvních pěti lichých čí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 následující výstup, kde můžeme vidět hierarchii dat zaměstnanců:

Nerekurzivní CTE

Běžný tabulkový výraz, který sám na sebe neodkazuje, se nazývá nerekurzivní CTE. Nerekurzivní CTE je jednoduchý a snáze pochopitelný, protože nepoužívá koncept rekurze. Podle syntaxe CTE bude každý dotaz CTE začínat znakem ' S ' klauzule následovaná názvem CTE a seznamem sloupců, poté AS se závorkami.

Nevýhody CTE

Níže jsou uvedena omezení používání CTE v SQL Server:

• Členové CTE nemohou používat klíčové klauzule jako Distinct, Group By, Having, Top, Joins atd.
• Rekurzivní člen může odkazovat na CTE pouze jednou.
• Nemůžeme použít proměnné tabulky a CTE jako parametry v uložených procedurách.
• Již víme, že CTE lze použít místo pohledu, ale CTE nelze vnořit, zatímco pohledy ano.
• Vzhledem k tomu, že je to pouze zkratka pro dotaz nebo poddotaz, nelze jej znovu použít v jiném dotazu.
• Počet sloupců v argumentech CTE a počet sloupců v dotazu musí být stejný.