Le espressioni di tabella comuni o CTE in breve vengono utilizzate in SQL Server per semplificare join e sottoquery complessi e per fornire un mezzo per interrogare dati gerarchici come un organigramma. In questo articolo, ti presenteremo le espressioni di tabella comuni, i due tipi di CTE e i loro usi. Inoltre, introdurremo il generale di CTE., Una volta che hai familiarità, ti incoraggio vivamente a leggere anche questi articoli:
- CTE non ricorsive
- CTE ricorsive
Introduzione alle espressioni di tabella comuni
Un CTE (Common Table Expression) è un set di risultati temporanei a cui puoi fare riferimento all’interno di un’altra istruzione SELECT, INSERT, UPDATE o DELETE. Sono stati introdotti in SQL Server versione 2005. Sono conformi a SQL e fanno parte delle specifiche ANSI SQL 99.
Un CTE restituisce sempre un set di risultati., Vengono utilizzati per semplificare le query, ad esempio, è possibile utilizzarne una per eliminare una tabella derivata dal corpo della query principale.
Nota: Tutti gli esempi di questa lezione sono basati su Microsoft SQL Server Management Studio e il database AdventureWorks2012. È possibile iniziare a utilizzare questi strumenti gratuiti utilizzando la mia guida Per iniziare a utilizzare SQL Server.
Che cos’è un CTE o un’espressione di tabella comune in SQL Server?
Un CTE (Common Table Expression) definisce un set di risultati temporanei che è possibile utilizzare in un’istruzione SELECT. Diventa un modo conveniente per gestire query complicate.,
Le espressioni di tabella comuni sono definite all’interno dell’istruzione utilizzando l’operatore WITH. In questo modo è possibile definire una o più espressioni di tabella comuni.
Ecco un esempio molto semplice di un CTE:
WITH Employee_CTE (EmployeeNumber, Title)AS(SELECT NationalIDNumber, JobTitle FROM HumanResources.Employee)SELECT EmployeeNumber, TitleFROM Employee_CTE
Scomponiamolo un po’.
La porzione blu è il CTE. Si noti che contiene una query che può essere eseguita da sola in SQL., Questo è chiamato il CTE definizione della query:
SELECT NationalIDNumber, JobTitleFROM HumanResources.Employee
Quando si vedono i risultati come:
si Noti che, quando si definisce la CTE diamo il risultato di un nome oltre che per le sue colonne. In questo modo un CTE agisce come una VISTA. Il risultato e le colonne sono denominati in modo diverso. Ciò consente di incapsulare la logica di query complicata con l’espressione di tabella comune.
Ora tornando al CTE, si noti che l’istruzione WITH., Lì vedrai il nome e le colonne sono definite. Queste colonne corrispondono alle colonne restituite dalla query interna.
Infine notate che la nostra query finale fa riferimento al CTE e alle colonne definite.
Dal punto di vista della nostra query esterna tutto ciò che “vede” è questa definizione. Non è interessato a come viene costruito il CTE, solo il suo nome e le sue colonne.,
In quanto tali, i risultati del CTE sono:
Notare i nomi delle colonne, sono basati su quelli definiti nel CTE.
Voglio sottolineare che è possibile definire più di un CTE all’interno di un’istruzione WITH. Questo può aiutarti a semplificare alcune query molto complicate che alla fine vengono unite insieme. Ogni pezzo complicato può includere nel proprio CTE che viene quindi indicato e unito al di fuori della clausola WITH.,
Ecco un esempio di utilizzo di DUE CTE, è un semplice esempio, ma mostra come vengono definiti due CTE e quindi utilizzati in un JOIN INTERNO
WITH PersonCTE (BusinessEntityID, FirstName, LastName)AS (SELECT Person.BusinessEntityID, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'C%'),PhoneCTE (BusinessEntityID, PhoneNumber)AS (SELECT BusinessEntityID, PhoneNumber FROM Person.PersonPhone)SELECT FirstName, LastName, PhoneNumberFROM PersonCTEINNER JOINPhoneCTEON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;
La prima espressione di tabella comune è colorata di verde, la seconda di blu. Come puoi vedere dall’istruzione SELECT, i CTE sono uniti come se fossero tabelle. Speriamo che tu possa vedere che man mano che le tue query diventano più complicate, le CTE possono diventare un modo davvero utile per separare le operazioni; quindi, semplifica la tua query finale.
Perché hai bisogno di CTE?,
Ci sono diversi motivi per cui si consiglia di utilizzare un CTE rispetto ad altri metodi. Alcuni di questi includono:
- Leggibilità – CTE promuove la leggibilità. Piuttosto che raggruppare tutta la logica di query in un’unica query di grandi dimensioni, creare diversi CTE, che sono combinati più avanti nell’istruzione. Ciò consente di ottenere i blocchi di dati necessari e combinarli in una selezione finale.
- Sostituire una vista – È possibile sostituire un CTE per una vista. Questo è utile se non si dispone delle autorizzazioni per creare un oggetto di visualizzazione o se non si desidera crearne uno in quanto viene utilizzato solo in questa query.,
- Ricorsione-Usa CTE crea query ricorsive, cioè query che possono chiamarsi. Questo è utile quando è necessario lavorare su dati gerarchici come organigrammi.
- Limitazioni-Superare le limitazioni dell’istruzione SELECT, come il riferimento stesso (ricorsione) o l’esecuzione di un GRUPPO utilizzando funzioni non deterministiche.
- Classifica-Ogni volta che si desidera utilizzare la funzione di classifica come ROW_NUMBER (), RANK (), NTILE () ecc.
Tipi di CTE
Le espressioni di tabella comuni possono essere collocate in due grandi categorie: CTE ricorsive e CTE non ricorsive.,
I CTE ricorsivi sono espressioni di tabella comuni che fanno riferimento a se stessi. La ricorsione può essere un argomento piuttosto difficile da afferrare, non l’ho capito fino a quando non ho preso una classe LISP nel lontano 1986, ma spero di poterlo spiegare a te.
Approfondiremo la ricorsione in un post separato, ma per ora lascia che ti presenti la ricorsione usando questo diagramma:
Qui vedi l’immagine di specchi opposti. A causa del riflesso, diventa un’immagine in un’immagine.
Le query ricorsive sono così.,
Quando viene eseguita una query ricorsiva, viene eseguita ripetutamente su un sottoinsieme dei dati. Una query ricorsiva è fondamentalmente una query che si chiama. Ad un certo punto c’è una condizione finale, quindi non si chiama indefinitamente.
In un certo senso quando guardi nell’immagine puoi immaginare che ogni immagine in un’immagine sia l’immagine che si fa chiamare. Tuttavia, a differenza della “riflessione infinita” negli specchi, arriva un punto in cui una query ricorsiva incontra la condizione finale e smette di chiamarsi.,
A quel punto, la ricorsione inizia a svolgere, raccogliere e calcolare i dati mentre rivede ogni risultato successivo.
I CTE non ricorsivi, come suggerisce il nome, non usano la ricorsione. Non fanno riferimento a se stessi. Sono più facili da capire, quindi li esamineremo prima in dettaglio nel prossimo articolo di questa serie.
Conclusione
Speriamo che tu ora abbia un apprezzamento di cosa sono i CTE e perché potremmo volerli usare. Nei prossimi due articoli andremo in dettaglio molto maggiore su CTE, e quando usarli.,
Fino ad allora, consiglierei di rivedere i miei articoli su join e subquery poiché attingeremo a questi tipi di query per i nostri esempi.