Espressione della tabella comune del server SQL

L'espressione della tabella comune o il CTE è il set di risultati nominato, che è stato introdotto in SQL Server 2005. L'espressione della tabella comune funge da tabella virtuale con record e colonne create durante l'esecuzione di una query con CTE e rilasciata dopo il completamento della query. Può essere referenziato all'interno di qualsiasi istruzione selezionata, inserto, aggiornamento o elimina. Questo viene utilizzato anche per creare una vista.

CTE può essere definito dalla sintassi di seguito.

[CON [… ]]
Nome di CTE [(nome delle colonne [,…])]
AS (query di CTE)
Seleziona * da CTE

Esempio:

Con cte_name (column1, column2, column3)
COME
(
Seleziona colonna1, colonna2, colonna3
Dalla tabella 1
Dove colonna1> 500
)

Secondo l'esempio, dopo aver definito CTE CTE_Name, possiamo usare il CTE immediatamente dopo averlo definito come una tabella. Di seguito è riportato un esempio:

Seleziona da cte_name

Restituirà l'output di tre colonne, column1, colonna2 e colonna3.

Può essere utilizzato anche nelle dichiarazioni di inserto, eliminare, aggiornare e unire. Mostreremo un esempio di ciascuno di seguito.

CTE multiplo

CTE multipli può essere utilizzato in una singola query.

Con cte_name1 (column1, column2, column3)
COME
(
Seleziona colonna1, colonna2, colonna3
Dalla tabella 1
Dove colonna1> 100
)
COME
(
Seleziona * da cte_name2
dove colonna2> 200
)
Seleziona * da cte_name2

La query sopra restituirà i record dalla tabella 1 dove colonna1 è maggiore di 100 e colonna2 è maggiore di 200.

Elimina usando CTE

CTE può essere molto utile per eliminare i record da una tabella.

Con cte_name (column1, column2, column3)
COME
(
Seleziona colonna1, colonna2, colonna3
Dalla tabella 1
Dove colonna1> 100
)
Elimina da cte_name

L'istruzione sopra eliminerà i record dalla tabella di base: Tabella 1 dove il valore di colonna1 è superiore a 100.

Questo è anche il modo efficiente per eliminare le voci duplicate da una tabella. Di seguito è riportato l'esempio.

Con cte_name (id, column1, column2, column3, rn)
COME
(
Seleziona ID, Column1, Column2, Column3, Row_Number () Over (partizione per ID Order per ID) come RN
Dalla tabella 1
)
Elimina da cte_name
Dove cte_name. Rn> 1

Questo eliminerà tutte le righe duplicate dalla tabella 1.

Inserire usando CTE

Possiamo inserire un set di dati specifico che è definito in un CTE in un'altra tabella. Guarda l'esempio seguente.

Con cte_insert (id, column1, column2, column3)
COME
(
Seleziona ID, Column1, Column2, Column3
Dalla tabella 1
Dove colonna1> 200
)
/* per l'inserimento in una tabella esistente Dest_table*/
Inserisci in dest_table (column1, column2, column3)
Seleziona colonna1, colonna2, colonna3 da cte_insert
/ * Per la creazione di una nuova tabella Dest_table_new e inserire i dati del CTE */
Seleziona colonna1, colonna2, colonna3
In dest_table_new

L'istruzione sopra creerà la tabella con le tre colonne-colonne1, colonna2, colonna3 e inserisci i dati in essa.

Aggiornamento utilizzando CTE

Il concetto di aggiornamento utilizzando CTE è lo stesso dell'inserimento e della cancellazione. Controlliamo qui sotto l'esempio.

Con cte_update (id, column1, column2, column3)
COME
(
Seleziona ID, Column1, Column2, Column3
Dalla tabella 1
Dove colonna1> 200
)
/* Aggiorna la tabella di base- Tabella 1, di CTE per aumentare il valore della colonna1 di 100*/
Aggiorna cte_update
Imposta colonna1 = colonna1+100
/*Aggiorna un'altra tabella - dest_table, usando il valore di cte*/
Aggiornamento a
Imposta a.colonna1 = b.colonna1
da dest_table a
Unisciti a cte_update b
su a.id = b.id

Unire usando CTE

Fare riferimento all'esempio seguente per una migliore comprensione.

Con src_cte (id, column1, column2, column3)
COME
(
Seleziona ID, Column1, Column2, Column3 da SRC_Table
)
Unire
TGT_TBL come target
Usando src_cte come sorgente
Sull'obiettivo.id = fonte.id)
Se abbinato allora
Aggiornamento imposta Target.Colonna1 = sorgente.Colonna1,
bersaglio.Colonna2 = sorgente.Colonna2,
bersaglio.Colonna3 = sorgente.Colonna3
Se non abbinato allora
INSERT (colonna1, colonne2, colonne3) valori (sorgente.Colonna1, sorgente.Colonna2, sorgente.Colonna3);

Nella query sopra, stiamo cercando di caricare i dati in modo incrementale da src_table a tgt_table.

In che modo la variabile CTE, Temp e TEMP sono differiti in SQL Server?

Dagli ultimi esempi, conosciamo gli usi di CTE e abbiamo avuto una chiara idea di ciò che è CTE. Ora, la differenza tra CTE è e la tabella temporanea e la variabile temp è:

• • CTE ha sempre bisogno di memoria ma le tabelle della temp hanno bisogno di un disco. La variabile della tabella utilizza entrambi. Quindi, non dovremmo usare CTE quando c'è più volume di dati.
  • L'ambito della variabile della tabella è solo per il lotto e l'ambito della tabella temporanea è per la sessione e l'ambito di CTE è solo per la query.

Conclusione

CTE può essere utile quando è necessario generare un set di risultati temporanei e è possibile accedervi in ​​selezione, inserimento, aggiornamento, elimina e unisci. Può essere molto ottimizzato in termini di CPU e usi della memoria.