Utilitzarem les expressions de taula comuns o CTE d'SQL Server per facilitar les unions i les subconsultes complexes. També proporciona una manera de consultar dades jeràrquiques, com ara una jerarquia organitzativa. Aquest article ofereix una visió general completa de CTE, tipus de CTE, avantatges, desavantatges i com utilitzar-los a SQL Server.

Què és CTE a SQL Server?

Un CTE (Common Table Expression) és un conjunt de resultats únic que només existeix durant la durada de la consulta. . Ens permet fer referència a dades dins d'un únic àmbit d'execució de la instrucció SELECT, INSERT, UPDATE, DELETE, CREATE VIEW o MERGE. És temporal perquè el seu resultat no es pot emmagatzemar enlloc i es perdrà tan bon punt es completi l'execució d'una consulta. Va venir per primera vegada amb la versió SQL Server 2005. Un DBA sempre ha preferit que el CTE l'utilitzi com a alternativa a una subconsulta/visualització. Segueixen l'estàndard ANSI SQL 99 i són compatibles amb SQL.

Sintaxi CTE a SQL Server

La sintaxi CTE inclou un nom CTE, una llista de columnes opcional i una instrucció/consulta que defineix l'expressió de taula comuna (CTE). Després de definir el CTE, el podem utilitzar com a vista en una consulta SELECT, INSERT, UPDATE, DELETE i MERGE.

La següent és la sintaxi bàsica de CTE a SQL Server:

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

En aquesta sintaxi:

• Primer hem especificat el nom del CTE al qual es farà referència més endavant en una consulta.
• El següent pas és crear una llista de columnes separades per comes. Assegura que el nombre de columnes dels arguments de definició CTE i el nombre de columnes de la consulta han de ser el mateix. Si no hem definit les columnes dels arguments CTE, utilitzarà les columnes de consulta que defineixen el CTE.
• Després d'això, utilitzarem la paraula clau AS després del nom de l'expressió i després definirem una instrucció SELECT el conjunt de resultats de la qual omple el CTE.
• Finalment, utilitzarem el nom CTE en una consulta com SELECT, INSERT, UPDATE, DELETE i MERGE.

S'ha de tenir en compte mentre escriu la definició de la consulta CTE; no podem utilitzar les clàusules següents:

1. ORDER BY tret que també utilitzeu com a clàusula TOP
2. INTO
3. clàusula OPTION amb suggeriments de consulta
4. PER NAVEGAR

La imatge següent és la representació de la definició de la consulta CTE.

Aquí, la primera part és una expressió CTE que conté una consulta SQL que es pot executar de manera independent a SQL. I la segona part és la consulta que utilitza el CTE per mostrar el resultat.

Exemple

Entendrem com funciona CTE a SQL Server utilitzant diversos exemples. Aquí, farem servir una taula ' client 'per a una manifestació. Suposem que aquesta taula conté les dades següents:

En aquest exemple, el nom CTE és clients_a_novayork , la subconsulta que defineix el CTE retorna les tres columnes nom del client, correu electrònic, i estat . Com a resultat, el CTE customers_in_newyork retornarà tots els clients que visquin a l'estat de Nova York.

Després de definir el CTE customers_in_newyork, l'hem fet referència al SELECCIONA declaració per obtenir els detalls d'aquells clients que es troben a Nova York.

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

Després d'executar la instrucció anterior, donarà la següent sortida. Aquí, podem veure que el resultat només retorna la informació del client que es troba a l'estat de Nova York.

CTE múltiple

En alguns casos, haurem de crear diverses consultes CTE i unir-les per veure els resultats. Podem utilitzar diversos conceptes de CTE en aquest escenari. Hem d'utilitzar l'operador de coma per crear diverses consultes CTE i combinar-les en una sola instrucció. L'operador de coma ',' ha d'anar precedit del nom CTE per distingir diversos CTE.

Diversos CTE ens ajuden a simplificar les consultes complexes que finalment s'ajunten. Cada peça complexa tenia el seu propi CTE, que després es podia referenciar i unir fora de la clàusula WITH.

NOTA: La definició de CTE múltiple es pot definir mitjançant UNION, UNION ALL, JOIN, INTERSECT o EXCEPT.

La sintaxi següent ho explica amb més claredat:

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

Exemple

Entendrem com funcionen diversos CTE a SQL Server. Aquí, farem servir l'anterior ' client ' taula per a una demostració.

En aquest exemple, hem definit els dos noms CTE clients_a_novayork i clients_a_california . Aleshores, el conjunt de resultats de subconsultes d'aquests CTE omple el CTE. Finalment, utilitzarem els noms de CTE en una consulta que retornarà tots els clients que es troben a Nova York i Estat de Califòrnia .

 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; 

Estat de Nova York i Califòrnia.

Per què necessitem CTE?

Igual que les visualitzacions de bases de dades i les taules derivades, els CTE poden facilitar l'escriptura i la gestió de consultes complexes fent-les més llegibles i senzilles. Podem aconseguir aquesta característica desglossant les consultes complexes en blocs simples que es poden reutilitzar per reescriure la consulta.

Alguns dels seus casos d'ús es mostren a continuació:

• És útil quan necessitem definir una taula derivada diverses vegades dins d'una sola consulta.
• És útil quan necessitem crear una alternativa a una vista a la base de dades.
• És útil quan necessitem realitzar el mateix càlcul diverses vegades en diversos components de consulta simultàniament.
• És útil quan necessitem utilitzar funcions de classificació com ROW_NUMBER(), RANK() i NTILE().

Alguns dels seus avantatges es detallen a continuació:

vaja

• CTE facilita el manteniment del codi més fàcil.
• CTE augmenta la llegibilitat del codi.
• Augmenta el rendiment de la consulta.
• CTE permet implementar consultes recursives fàcilment.

Tipus de CTE a SQL Server

SQL Server divideix el CTE (Common Table Expressions) en dues grans categories:

1. CTE recursiu
2. CTE no recursiu

CTE recursiu

Una expressió de taula comuna es coneix com a CTE recursiu que fa referència a si mateix. El seu concepte es basa en la recursivitat, que es defineix com ' l'aplicació d'un procés recursiu o definició repetidament .' Quan executem una consulta recursiva, itera repetidament sobre un subconjunt de dades. Simplement es defineix com una consulta que es diu a si mateixa. Hi ha una condició final en algun moment, de manera que no s'anomena infinitament.

Un CTE recursiu ha de tenir a UNIÓ TOTS declaració i una segona definició de consulta que fa referència al propi CTE per tal de ser recursiu.

Exemple

Entenem com funciona CTE recursiu a SQL Server. Considereu la declaració següent, que genera una sèrie dels cinc primers nombres senars:

 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>

Aquest CTE donarà la següent sortida on podem veure la jerarquia de les dades dels empleats:

CTE no recursiu

Una expressió de taula comuna que no fa referència a si mateixa es coneix com a CTE no recursiu. Un CTE no recursiu és senzill i més fàcil d'entendre perquè no utilitza el concepte de recursivitat. Segons la sintaxi CTE, cada consulta CTE començarà amb un ' Amb clàusula seguida del nom del CTE i la llista de columnes, després AS amb parèntesis.

Inconvenients del CTE

A continuació es mostren les limitacions de l'ús de CTE a SQL Server:

• Els membres del CTE no poden utilitzar les clàusules de paraules clau com Distinct, Group By, Having, Top, Joins, etc.
• El membre recursiu només pot fer referència al CTE una vegada.
• No podem utilitzar les variables de la taula i els CTE com a paràmetres en els procediments emmagatzemats.
• Ja sabem que el CTE es podria utilitzar en lloc d'una vista, però un CTE no es pot niar, mentre que les vistes sí.
• Com que només és una drecera per a una consulta o subconsulta, no es pot reutilitzar en una altra consulta.
• El nombre de columnes dels arguments CTE i el nombre de columnes de la consulta han de ser el mateix.