Utilizzo delle espressioni di tabella comuni
Un'espressione di tabella comune (CTE) può essere considerata un set di risultati temporaneo definito nell'ambito di esecuzione di una singola istruzione SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Una CTE è simile a una tabella derivata poiché non è archiviata come un oggetto e viene mantenuta solo per la durata della query. A differenza di una tabella derivata, una CTE può essere autoreferenziale e vi si può fare riferimento più volte all'interno della stessa query.
È possibile utilizzare le CTE per:
Creare una query ricorsiva. Per ulteriori informazioni, vedere Query ricorsive tramite espressioni di tabella comuni.
Sostituire una vista quando non ne è richiesto un utilizzo generale, ovvero non è necessario archiviare la definizione nei metadati.
Abilitare il raggruppamento in base a una colonna derivata da un'istruzione di selezione secondaria scalare o a una funzione non deterministica o che ha accesso esterno.
Fare riferimento alla tabella risultante più volte nella stessa istruzione.
L'utilizzo di una CTE ha il vantaggio di una maggiore leggibilità e facilità nella gestione di query complesse. È possibile dividere la query in blocchi di compilazione logici separati e più semplici. È possibile quindi utilizzare questi blocchi semplici per compilare CTE provvisorie più complesse finché non viene generato il set di risultati finale.
È possibile definire CTE all'interno di routine definite dall'utente, quali funzioni, stored procedure, trigger o viste.
Struttura di una CTE
Una CTE è costituita da un nome di espressione che rappresenta la CTE, un elenco di colonne facoltativo e una query che definisce la CTE. Dopo che è stata definita, è possibile fare riferimento a una CTE in un'istruzione SELECT, INSERT, UPDATE o DELETE allo stesso modo di una tabella o vista. È inoltre possibile utilizzare le CTE nelle istruzioni CREATE VIEW come parte della corrispondente istruzione SELECT di definizione.
Di seguito viene illustrata la struttura della sintassi di base di una CTE:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
L'elenco di nomi di colonne è facoltativo solo se nella definizione della query vengono specificati nomi distinti per tutte le colonne risultanti.
Di seguito viene riportata l'istruzione per eseguire la CTE:
SELECT <column_list>
FROM expression_name;
Esempio
Nell'esempio seguente vengono illustrati i componenti della struttura della CTE, ovvero nome dell'espressione, elenco di colonne e query. L'espressione CTE Sales_CTE presenta tre colonne (SalesPersonID, SalesOrderID, and OrderDate) ed è definita come numero totale di ordini di vendita all'anno per tutti i venditori.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Di seguito viene fornito un set di risultati parziale:
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003