Le Common Table Expression (CTE) che cosa sono?

Buongiorno a tutti! Ben ritrovati!

Oggi parliamo di Common Table Expression a cui molto più spesso ci si riferisce con l'acronimo CTE.

Cosa sono? Cercherò di spiegarlo nel modo più semplice possibile!

Pronti? Via!

 

Le Common Table Expression

Quando mi fanno questa domanda rispondo utilizzando il modo più semplice in cui riesco a definirle:

Una CTE è un Result set temporaneo a cui associamo un nome ed al quale possiamo riferirci nei vari statements.

Le CTE sono state introdotte ormai un po' di anni fa con il rilascio della versione 2005 di SQL Server ma se non sapete quale versione è installata potete vedere qui: identificare la versione di sql server installata

L'obbiettivo era quello di prevedere una sintassi estesa che permettesse di scrivere le Query in modo più agevole. Il loro utilizzo potrebbe inoltre portare ad un incremento delle prestazioni ma questo aspetto lo vedremo in futuro.

La sintassi base per l'utilizzo delle CTE è davvero semplice nel senso che è sufficiente anteporre al nostro statement INSERT, UPDATE o DELETE che sia la keyword WITH seguita dal nome che vogliamo dare alla Query, seguito a sua volta dalla keyword AS. 

Ad esempio cosi:

WITH MAXCODICE AS (SELECT MAX(CODICE) AS CODICE FROM ELENCO)
SELECT * FROM MAXCODICE

Ora le CTE portano in dote la possibilità di scrivere Query ricorsive.

Vedremo quindi i due casi di CTE non ricorsive e ricorsive.

Un esempio di CTE non ricorsiva potrebbe essere questo sotto dove voglio estrarre tutti i codici della mia tabella ELENCO escluso il codice che ha valore massimo.

Potrei scrivere la Query così:

WITH MAXCODICE AS (SELECT MAX(CODICE) AS CODICE FROM ELENCO)
SELECT * FROM ELENCO E
WHERE NOT EXISTS (SELECT CODICE FROM MAXCODICE T WHERE T.CODICE = E.CODICE)

Come potete vedere la CTE è ad tutti gli effetti una tabella per cui posso andarci in JOIN.

Vediamo ora come utilizzare una CTE ricorsiva:

Per prima cosa creiamo una CTE di nome RIGHE.

Poi al suo interno inseriamo due statement di SELECT uniti da un operatore di UNION ALL

La prima SELECT è detta ANCORA.

La seconda SELECT innesca la ricorsione grazie al fatto che è possibile specificare il nome della CTE stessa (in questo case RIGHE)

WITH RIGHE AS
(
 SELECT 1 AS NUMERO
 UNION ALL
 SELECT NUMERO+1 FROM RIGHE WHERE NUMERO < 10
)
SELECT * FROM RIGHE

 

Eseguiamola per vedere che vengono ritornati proprio i numeri compresi tra 1 e 10.

Questo metodo è infatti utile ad esempio per generare tabella con un numero prefissato di righe.

Per default uscirà un errore se il numero di ricorsioni supera i valore di 100. 
E' possibile aumentare tale valore tramite il comando OPTION (MAXRECURSION N) 
Dove N può valere al massimo 32768.
Impostando invece N=0 otterremo una ricorsione infinita.

Per oggi è tutto, spero di essere riuscito nell'intento di spiegare nel modo più chiaro possibile l'argomento.
Se così non fosse scrivetemi..

Ciao,

Luca Biondi @ SQLServerPerformance blog!

 

 

 

 

 

 

Help me to share knowledge on my blog  

 

Next post: Di sottoquery ed ottimizzazioni (parte 1)

Previous post: Babbo Natale e gli indici univoci filtrati