Utilizzeremo le espressioni di tabella comuni o CTE di SQL Server per semplificare join e sottoquery complessi. Fornisce inoltre un modo per eseguire query su dati gerarchici, ad esempio una gerarchia organizzativa. Questo articolo offre una panoramica completa di CTE, tipi di CTE, vantaggi, svantaggi e come utilizzarli in SQL Server.
Che cos'è CTE in SQL Server?
Una CTE (Common Table Expression) è un set di risultati una tantum che esiste solo per la durata della query . Ci consente di fare riferimento ai dati all'interno di un singolo ambito di esecuzione dell'istruzione SELECT, INSERT, UPDATE, DELETE, CREATE VIEW o MERGE. È temporaneo perché il suo risultato non può essere archiviato da nessuna parte e verrà perso non appena l'esecuzione della query verrà completata. È arrivato per la prima volta con la versione SQL Server 2005. Un DBA ha sempre preferito utilizzare CTE come alternativa a una sottoquery/vista. Seguono lo standard ANSI SQL 99 e sono conformi a SQL.
Sintassi CTE in SQL Server
La sintassi CTE include un nome CTE, un elenco di colonne facoltativo e un'istruzione/query che definisce l'espressione di tabella comune (CTE). Dopo aver definito il CTE, possiamo usarlo come vista in una query SELECT, INSERT, UPDATE, DELETE e MERGE.
Quella che segue è la sintassi di base di CTE in SQL Server:
WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;
In questa sintassi:
- Abbiamo innanzitutto specificato il nome CTE a cui verrà fatto riferimento successivamente in una query.
- Il passaggio successivo consiste nel creare un elenco di colonne separate da virgole. Garantisce che il numero di colonne negli argomenti della definizione CTE e il numero di colonne nella query siano gli stessi. Se non abbiamo definito le colonne degli argomenti CTE, utilizzerà le colonne di query che definiscono il CTE.
- Successivamente, utilizzeremo la parola chiave AS dopo il nome dell'espressione e quindi definiremo un'istruzione SELECT il cui set di risultati popola il CTE.
- Infine, utilizzeremo il nome CTE in una query come l'istruzione SELECT, INSERT, UPDATE, DELETE e MERGE.
È opportuno tenerlo presente durante la scrittura della definizione della query CTE; non possiamo utilizzare le seguenti clausole:
- ORDER BY a meno che non venga utilizzata anche come clausola TOP
- IN
- Clausola OPTION con suggerimenti per la query
- PER SFOGLIARE
L'immagine seguente è la rappresentazione della definizione della query CTE.
In questo caso, la prima parte è un'espressione CTE che contiene una query SQL che può essere eseguita in modo indipendente in SQL. E la seconda parte è la query che utilizza CTE per visualizzare il risultato.
Esempio
Cerchiamo di capire come funziona CTE in SQL Server utilizzando vari esempi. Qui utilizzeremo una tabella ' cliente ' per una dimostrazione. Supponiamo che questa tabella contenga i seguenti dati:
In questo esempio, il nome CTE è clienti_a_newyork , la sottoquery che definisce la CTE restituisce le tre colonne nome del cliente, e-mail, E stato . Di conseguenza, il CTE clienti_in_newyork restituirà tutti i clienti che vivono nello Stato di New York.
Dopo aver definito il CTE clienti_in_newyork, ne abbiamo fatto riferimento nel file SELEZIONARE dichiarazione per ottenere i dettagli di quei clienti che si trovano a New York.
WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;
Dopo aver eseguito l'istruzione precedente, fornirà il seguente output. Qui possiamo vedere che il risultato restituisce solo le informazioni sui clienti che si trovano nello Stato di New York.
CTE multipli
In alcuni casi, dovremo creare più query CTE e unirle per visualizzare i risultati. In questo scenario potremmo utilizzare il concetto di CTE multipli. Dobbiamo utilizzare l'operatore virgola per creare più query CTE e unirle in un'unica istruzione. L'operatore virgola ',' deve essere preceduto dal nome CTE per distinguere più CTE.
Più CTE ci aiutano a semplificare query complesse che alla fine vengono unite insieme. Ogni pezzo complesso aveva il proprio CTE, a cui è possibile fare riferimento e unire all'esterno della clausola WITH.
NOTA: la definizione CTE multipla può essere definita utilizzando UNION, UNION ALL, JOIN, INTERSECT o EXCEPT.
La sintassi seguente lo spiega più chiaramente:
WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;
Esempio
Cerchiamo di capire come funzionano più CTE in SQL Server. Qui utilizzeremo quanto sopra ' cliente tavolo per una dimostrazione.
In questo esempio abbiamo definito i due nomi CTE clienti_a_newyork E clienti_in_california . Quindi il set di risultati delle sottoquery di queste CTE popola la CTE. Infine, utilizzeremo i nomi CTE in una query che restituirà tutti i clienti che si trovano in New York E Stato della 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 e lo Stato della California.
Perché abbiamo bisogno del CTE?
Come le visualizzazioni del database e le tabelle derivate, le CTE possono semplificare la scrittura e la gestione di query complesse rendendole più leggibili e semplici. Possiamo ottenere questa caratteristica suddividendo le query complesse in blocchi semplici che possono essere riutilizzati nella riscrittura della query.
Alcuni dei suoi casi d'uso sono riportati di seguito:
- È utile quando dobbiamo definire una tabella derivata più volte all'interno di una singola query.
- È utile quando dobbiamo creare un'alternativa ad una vista nel database.
- È utile quando dobbiamo eseguire lo stesso calcolo più volte contemporaneamente su più componenti della query.
- È utile quando dobbiamo utilizzare funzioni di classificazione come ROW_NUMBER(), RANK() e NTILE().
Alcuni dei suoi vantaggi sono riportati di seguito:
metodi di arraylist
- CTE facilita la manutenzione del codice.
- CTE aumenta la leggibilità del codice.
- Aumenta le prestazioni della query.
- CTE consente di implementare facilmente query ricorsive.
Tipi di CTE in SQL Server
SQL Server divide le CTE (Common Table Expressions) in due grandi categorie:
- CTE ricorsiva
- CTE non ricorsivo
CTE ricorsiva
Un'espressione di tabella comune è nota come CTE ricorsiva che fa riferimento a se stessa. Il suo concetto si basa sulla ricorsione, definita come ' l'applicazione ripetuta di un processo o di una definizione ricorsiva .' Quando eseguiamo una query ricorsiva, itera ripetutamente su un sottoinsieme di dati. È semplicemente definito come una query che chiama se stessa. Ad un certo punto c'è una condizione finale, quindi non si chiama infinito.
Una CTE ricorsiva deve avere un file UNIONE TUTTI e una seconda definizione di query che fa riferimento alla CTE stessa per essere ricorsiva.
Esempio
Cerchiamo di capire come funziona CTE ricorsivo in SQL Server. Considera la seguente affermazione, which genera una serie dei primi cinque numeri dispari:
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 ' <strong>jtp_employees</strong> ' 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'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 + ' ' + LastName AS FullName, EmpLevel, (SELECT FirstName + ' ' + 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'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 ' <strong>With</strong> ' 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's just a shortcut for a query or subquery, it can'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>
Questo CTE fornirà il seguente output in cui possiamo vedere la gerarchia dei dati dei dipendenti:
CTE non ricorsivo
Un'espressione di tabella comune che non fa riferimento a se stessa è nota come CTE non ricorsiva. Una CTE non ricorsiva è semplice e più facile da comprendere perché non utilizza il concetto di ricorsione. Secondo la sintassi CTE, ogni query CTE inizierà con un ' Con ' seguita dal nome CTE e dall'elenco delle colonne, quindi AS con parentesi.
Svantaggi del CTE
Di seguito sono riportate le limitazioni dell'utilizzo di CTE in SQL Server:
- I membri CTE non sono in grado di utilizzare le clausole di parole chiave come Distinct, Group By, Have, Top, Joins, ecc.
- Il membro ricorsivo può fare riferimento alla CTE solo una volta.
- Non è possibile utilizzare le variabili di tabella e le CTE come parametri nelle procedure memorizzate.
- Sappiamo già che la CTE potrebbe essere utilizzata al posto di una vista, ma una CTE non può essere annidata, mentre Views sì.
- Poiché si tratta semplicemente di una scorciatoia per una query o una sottoquery, non può essere riutilizzata in un'altra query.
- Il numero di colonne negli argomenti CTE e il numero di colonne nella query devono essere uguali.