MENU
  1. ホーム
  2. SQL
  3. Uso di tabelle temporanee e variabili di tabella in SQL

Uso di tabelle temporanee e variabili di tabella in SQL

SQL

È importante comprendere quando utilizzare una tabella temporanea o una variabile di tabella quando si memorizzano temporaneamente i dati in SQL. Entrambe offrono funzionalità simili, ma hanno caratteristiche e scenari di applicazione diversi. In questo articolo, spiegheremo in dettaglio le caratteristiche di ciascuno e esploreremo come fare la scelta migliore.

Indice

Caratteristiche delle tabelle temporanee

Le tabelle temporanee sono tabelle utilizzate per memorizzare dati temporanei in SQL, di solito utilizzando i prefissi # o ##.

Metodo di creazione

Le tabelle temporanee si creano utilizzando il comando CREATE TABLE. Ad esempio:

CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);

Uso

Le tabelle temporanee sono adatte per memorizzare grandi quantità di dati temporanei e per essere utilizzate ripetutamente in più istruzioni.

Prestazioni

Le tabelle temporanee memorizzano i dati su disco, offrendo prestazioni stabili anche per grandi volumi di dati. Tuttavia, possono essere influenzate dalle operazioni di I/O su disco.

Ambito

L’ambito di una tabella temporanea è limitato alla sessione o al batch. Una volta terminata la sessione, viene eliminata automaticamente.

-- Esempio di utilizzo all'interno di una sessione
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
SELECT * FROM #TempTable;

Caratteristiche delle variabili di tabella

Le variabili di tabella sono tabelle che possono essere trattate come variabili in SQL, utilizzate principalmente per operazioni temporanee sui dati.

Metodo di dichiarazione

Le variabili di tabella si dichiarano con il comando DECLARE. Ad esempio:

DECLARE @TableVar TABLE (
    ID INT,
    Name NVARCHAR(50)
);

Uso

Le variabili di tabella sono adatte per operare su piccole quantità di dati o per memorizzare dati temporanei all’interno di una procedura memorizzata.

Prestazioni

Le variabili di tabella memorizzano i dati in memoria, offrendo velocità nelle operazioni su piccoli volumi di dati. Tuttavia, non sono adatte per grandi quantità di dati.

Ambito

L’ambito di una variabile di tabella è limitato al batch o alla procedura memorizzata in cui è dichiarata. Viene rilasciata automaticamente quando esce dall’ambito.

-- Esempio di utilizzo all'interno di un batch
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @TableVar;

Confronto delle prestazioni

Le prestazioni delle tabelle temporanee e delle variabili di tabella variano in base alla quantità di dati e al tipo di operazione. Di seguito, confronteremo le differenze di prestazioni con esempi specifici.

Per piccole quantità di dati

Le variabili di tabella offrono prestazioni migliori per piccole quantità di dati. Poiché le operazioni avvengono in memoria, non c’è overhead dovuto alle operazioni di I/O su disco.

-- Esempio di buone prestazioni con una variabile di tabella
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Per grandi quantità di dati

Quando si gestiscono grandi quantità di dati, le tabelle temporanee sono più adatte. I dati vengono memorizzati su disco, quindi la memoria è meno limitata, ed è possibile creare indici.

-- Esempio di utilizzo di una tabella temporanea con grandi quantità di dati
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable;

Con o senza indici

Le tabelle temporanee permettono di creare indici per migliorare le prestazioni. Le variabili di tabella, invece, non permettono di creare indici, il che può ridurre le prestazioni in query su larga scala.

Gestione di query complesse

Le tabelle temporanee offrono prestazioni stabili anche in query complesse o nelle operazioni di join. Le variabili di tabella non memorizzano i piani di query, quindi le prestazioni possono risultare inferiori in operazioni complesse.

-- Esempio di utilizzo di una tabella temporanea in una query complessa
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
SELECT t1.ID, t2.Name
FROM #TempTable t1
JOIN AnotherTable t2 ON t1.ID = t2.ID;

Differenze di ambito e durata

Le tabelle temporanee e le variabili di tabella hanno ambiti e durate diversi. Comprendere queste differenze è fondamentale per scegliere il metodo più appropriato.

Ambito delle tabelle temporanee

Le tabelle temporanee sono valide all’interno della sessione o del batch in cui sono create. Vengono eliminate automaticamente al termine della sessione o del batch. Inoltre, le tabelle temporanee locali (#TempTable) sono visibili solo all’interno della sessione corrente, mentre le tabelle temporanee globali (##TempTable) possono essere accessibili da altre sessioni.

-- Esempio di ambito di una tabella temporanea
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
-- #TempTable verrà eliminata automaticamente al termine della sessione

Ambito delle variabili di tabella

Le variabili di tabella sono valide solo all’interno del batch o della procedura memorizzata in cui sono dichiarate. Vengono rilasciate automaticamente una volta uscite dall’ambito.

-- Esempio di ambito di una variabile di tabella
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
-- La variabile @TableVar viene rilasciata al termine del batch

Differenze nella durata

Le tabelle temporanee dipendono dalla durata della sessione. Se la sessione dura a lungo, la tabella temporanea rimarrà disponibile per tutta la durata. Al contrario, le variabili di tabella vengono rilasciate non appena il batch o la procedura memorizzata terminano.

Esempi di applicazione di ambito e durata

Le tabelle temporanee sono adatte quando è necessario condividere i dati tra più batch o procedure memorizzate durante l’intera sessione. Le variabili di tabella sono utili quando si desidera memorizzare temporaneamente i dati all’interno di un singolo batch o procedura memorizzata.

-- Esempio di applicazione di una tabella temporanea
CREATE TABLE #SessionTemp (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionTemp (ID, Name) VALUES (1, 'Alice');

-- Accesso da un altro batch o procedura memorizzata
SELECT * FROM #SessionTemp;

-- Esempio di applicazione di una variabile di tabella
DECLARE @BatchVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @BatchVar;
-- La variabile @BatchVar viene rilasciata al termine del batch

Scenari di applicazione di tabelle temporanee e variabili di tabella

Le tabelle temporanee e le variabili di tabella sono entrambe soluzioni ottimali in determinati scenari. Qui vengono illustrati esempi concreti di utilizzo.

Scenari in cui le tabelle temporanee sono adatte

Le tabelle temporanee sono particolarmente utili nei seguenti casi:

Gestione di grandi quantità di dati

Quando è necessario memorizzare grandi quantità di dati temporanei e operare su di essi più volte in diverse istruzioni, le tabelle temporanee sono la scelta giusta. I dati vengono memorizzati su disco, il che riduce i limiti imposti dalla memoria.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
SELECT * FROM #TempTable WHERE ID > 1000;

Creazione di indici

Quando è necessario creare indici per migliorare le prestazioni delle query, le tabelle temporanee sono adatte.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable WHERE ID > 1000;

Condivisione di dati tra sessioni

Le tabelle temporanee sono ideali quando è necessario condividere i dati in tutta la sessione, ad esempio tra più procedure memorizzate o batch.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
-- Accesso da un altro batch o procedura memorizzata
SELECT * FROM #TempTable;

Scenari in cui le variabili di tabella sono adatte

Le variabili di tabella sono particolarmente utili nei seguenti casi:

Gestione di piccole quantità di dati

Quando si opera su piccole quantità di dati, le variabili di tabella sono adatte. Le operazioni avvengono in memoria, offrendo velocità elevate.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Memorizzazione temporanea di dati

Le variabili di tabella sono adatte per memorizzare temporaneamente i dati all’interno di un singolo batch o procedura memorizzata.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
-- La variabile @TableVar viene rilasciata al termine del batch
SELECT * FROM @TableVar;

Uso all’interno dei trigger

Le variabili di tabella sono ideali anche per memorizzare temporaneamente i dati all’interno di un trigger. Poiché l’ambito di un trigger è limitato, la leggerezza delle variabili di tabella è un vantaggio.

CREATE TRIGGER trgAfterInsert ON SampleTable
AFTER INSERT AS
BEGIN
    DECLARE @InsertedData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @InsertedData (ID, Name)
    SELECT ID, Name FROM Inserted;
    -- Operazioni sui dati all'interno del trigger
    SELECT * FROM @InsertedData;
END;

Best practice per SQL

Qui sono presentate alcune best practice per utilizzare al meglio le tabelle temporanee e le variabili di tabella, per migliorare le prestazioni e l’efficienza delle query SQL.

Scegliere in base alla quantità di dati

Per operare su piccole quantità di dati, utilizzare variabili di tabella; per grandi quantità di dati, utilizzare tabelle temporanee per ottimizzare le prestazioni.

-- Utilizzare variabili di tabella per piccole quantità di dati
DECLARE @SmallData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @SmallData (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @SmallData;

-- Utilizzare tabelle temporanee per grandi quantità di dati
CREATE TABLE #LargeData (ID INT, Name NVARCHAR(50));
INSERT INTO #LargeData (ID, Name) SELECT ID, Name FROM LargeSourceTable;
SELECT * FROM #LargeData;

Sfruttare gli indici

Creare indici sulle tabelle temporanee per migliorare le prestazioni delle query sui grandi volumi di dati.

CREATE TABLE #IndexedTable (ID INT, Name NVARCHAR(50));
INSERT INTO #IndexedTable (ID, Name) SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #IndexedTable(ID);
SELECT * FROM #IndexedTable WHERE ID > 1000;

Progettare in base all’ambito

Progettare con attenzione l’ambito e la durata dei dati per evitare un consumo eccessivo di risorse.

-- Utilizzare tabelle temporanee per dati da utilizzare per l'intera sessione
CREATE TABLE #SessionData (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionData (ID, Name) VALUES (1, 'Alice');
-- Accesso da batch diversi
SELECT * FROM #SessionData;

-- Utilizzare variabili di tabella per dati da utilizzare solo all'interno di un batch
DECLARE @BatchData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchData (ID, Name) VALUES (1, 'Alice');
-- La variabile @BatchData viene rilasciata al termine del batch
SELECT * FROM @BatchData;

Facilità di manutenzione

Le variabili di tabella vengono rilasciate automaticamente al termine del batch, prevenendo potenziali perdite di memoria e semplificando la manutenzione, in particolare nelle procedure memorizzate.

-- Esempio di utilizzo di una variabile di tabella all'interno di una procedura memorizzata
CREATE PROCEDURE SampleProcedure
AS
BEGIN
    DECLARE @ProcData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @ProcData (ID, Name) VALUES (1, 'Alice');
    SELECT * FROM @ProcData;
END;

Test e monitoraggio delle prestazioni

È importante testare ogni scenario e monitorare le prestazioni reali per fare la scelta più appropriata.

-- Eseguire test su scenari reali
-- Utilizzare strumenti di monitoraggio delle prestazioni per verificare l'efficienza

Conclusione

Riassumiamo i punti chiave per differenziare l’uso delle tabelle temporanee e delle variabili di tabella.

Le tabelle temporanee sono più adatte per la gestione di grandi quantità di dati o quando è necessario creare indici, e sono utili per condividere dati durante l’intera sessione. Le variabili di tabella sono ideali per operazioni su piccole quantità di dati o per memorizzazioni temporanee all’interno di un singolo batch, offrendo un’elaborazione veloce in memoria. Scegliere il metodo giusto in base allo scenario ottimizza le prestazioni delle query SQL.

SQL
Ambito Archiviazione Dati Grandi Dati Indicizzazione Piccoli Dati Prestazioni SQL Tabella Temporanea Variabile di Tabella
Indice