Sql Server: le tabelle temporanee e le variabili di tipo tabella

Prima che, presi dallo sconforto (“Noooo, ancora un articolo sulle tabelle temporanee e sulle variabili tabella!!!!”), vi diate alla fuga gettandovi dalla finestra, spariate al computer o – peggio! – spariate a me, considerate che questo almeno è in italiano, è il frutto di una ricerca da diverse fonti (inglesi) che poi citerò in fondo all’articolo e che potreste comunque scoprire qualcosa di interessante.

Una leggenda metropolitana, a cui per molto tempo avevo creduto anch’io, vuole che le tabelle temporanee siano create nel database temporaneo [tempdb], mentre le variabili di tabella siano residenti in RAM e quindi molto più efficienti. A portarmi sulla cattiva strada era stata questa semplice prova:

DECLARE @temp TABLE
(
    TableKey int IDENTITY
  , TableAttribute char(1) NOT NULL
);
SELECT [@temp] = Object_Id( 'tempdb..@temp' );
GO

CREATE TABLE #temp
(
    TableKey int IDENTITY
  , TableAttribute char(1) NOT NULL
);
SELECT [#temp] = Object_Id( 'tempdb..#temp' );
GO


Questo semplice codice restituisce questi risultati:

@temp
-----------
NULL


#temp
-----------
2069582411

Questi risultati sembrerebbero confermare il fatto che la variabile di tipo tabella non risiede nel database temporaneo; in realtà a ben vedere, questi risultati confermano solo che la variabile non è accessibile attraverso il database temporaneo, che è cosa ben diversa.

“Una variabile di tabella non è una struttura di sola memoria.”

La frase è virgolettata perché non è mia, ma arriva da una fonte abbastanza autorevole: la Knowledge Base della stessa Microsoft!

Qui si scopre che in realtà anche le variabili di tipo tabelle vengono create nel database tempdb, ma se la memoria è sufficiente vengono create e elaborate mentre si trovano nella cache di dati: è interessante notare però che questo discorso vale – tale e quale – anche per le tabelle temporanee.

Nel suo articolo Yet Another Temp Tables Vs Table Variables Article, Derek Dieter mostra come, dato lo stesso codice, tanto le variabili di tabella quanto le tabelle temporanee scrivano la stessa quantità di dati sul disco.

Il fatto che le variabili di tabella vengano create nel database tempdb, comporta il fatto che la variabile avrà la stessa collation del database temporaneo; ciò deve essere tenuto nel debito conto qualora si metta in join una variabile tabella con una tabella di un database avente una collation diversa. Anche in questo caso, comunque, non ci sono differenze rispetto al comportamento di una tabella temporanea.

Le differenze tra i due tipi di tabella sono quindi altre.

Innanzitutto, le variabili di tabella hanno un ambito ben delimitato, che le rende ottime da usare come parametri o risultati di stored procedure e user function. La tabelle temporanee invece possono essere condivise – ad esempio – tra una stored procedure ed il codice chiamante (oppure il chiamato): riconosco che si tratta di un opinione personale, ma l’impossibilità di incapsulare e riutilizzare il codice mi sembra procurare più problemi che vantaggi.

L’ambito limitato della variabile di tipo tabella ha una conseguenza, che a prima vista sembra senz’altro positiva: una variabile tabella usata all’interno di una transazione non ne viene in realtà coinvolta, quindi la COMMIT o la ROLLBACK della transazione risulteranno più veloci rispetto alla stessa transazione in cui venisse usata una tabella temporanea. C’è però un effetto collaterale che bisogna conoscere: proprio perché non è interessata dalla transazione, un eventuale ROLLBACK lascerebbe comunque invariata la variabile di tipo tabella, laddove una tabella temporanea verrebbe invece riportata allo stato precedente.

USE tempdb;
GO
DECLARE @table TABLE ( TableKey int , TableAttribute char(80) NOT NULL );
BEGIN TRANSACTION
    INSERT INTO @table
    SELECT TOP 5 [object_id], [name] FROM sys.objects;

ROLLBACK
SELECT * FROM @table;
GO


L'esecuzione di questo codice mostrerà la tabella popolata con le prime 5 righe della sys.objects:

(5 row(s) affected)

TableKey TableAttribute
----------- --------------------------------------------------------------------------------
4 sysrowsetcolumns
5 sysrowsets
7 sysallocunits
8 sysfiles1
13 syshobtcolumns

(5 row(s) affected)

Proviamo ora a riscrivere il codice usando una tabella temporanea:

USE tempdb;
GO

CREATE TABLE #table ( TableKey int , TableAttribute char(255) NOT NULL );

BEGIN TRANSACTION
    INSERT INTO #table
    SELECT TOP 5 [object_id], [name] FROM sys.objects;

ROLLBACK

SELECT * FROM #table;


Il suo risultato è questo:

(5 row(s) affected)

TableKey TableAttribute

----------- --------------------------------------------------------------------------------

(0 row(s) affected)

E’ opportuno tenere molto bene presente questa differenza, perché a seconda delle circostanze potrebbe essere preferibile uno piuttosto che l’altro comportamento.

Le tabelle temporanee sono tabelle a tutti gli effetti: su di esse si possono applicare tutte le istruzioni di DDL previste per una qualsiasi tabella utente; sulle tabelle temporanee può essere eseguita l’istruzione TRUNCATE TABLE, mentre su una variabile di tabella l’istruzione TRUNCATE TABLE non può essere eseguita; al contrario delle tabelle temporanee, le variabili di tipo tabella possono avere solo una chiave primaria oppure un indice univoco (a partire da Sql Server 2014 anche le variabili di tipo tabella potranno avere indici non univoci).

Le variabili di tabella non conservano le statistiche, al contrario delle tabelle temporanee. Di conseguenza, l’optimizer costruirà un piano di esecuzione presumendo che la variabile di tabella sia vuota; da ciò deriva il fatto che l’optimizer molto probabilmente non riuscirà ad ottimizzare il codice per un numero “troppo alto” di righe nella variabile di tipo tabella: la stessa Microsoft suggerisce di usare variabili di tipo tabella solo se questa conterrà non più di 100 righe, anche se in diversi blog si può tovare questo limite innalzato a 1000 righe.

In conseguenza della mancata manutenzione delle statistiche, la variabile tabella consente meno ricompilazioni del codice di una stored procedure o di una funzione rispetto ad una tabella temporanea. La ricompilazione avviene infatti non solo nel caso di un cambiamento nella definizione della struttura della tabella, ma anche solo per la variazione della dimensione della stessa; ma come abbiamo visto l’optimizer considera le variabili di tabella sempre come vuote.

Immaginiamo ad esempio la classica stored procedure di paginazione dei dati che ci consente di passare come parametro il numero di record desiderati: invocando la SP una prima volta richiedendo 1 solo record, poi richiedendo 5000 record, indurremmo il Query Engine di Sql Server a considerare non più valido il piano di esecuzione nella cache e quindi a ricompilare la query.

Per ovviare a questa ricompilazione abbiamo due alternative: usare l’opzione OPTION (KEEPFIXED PLAN) nella SELECT, oppure usare una variabile tabella invece di una tabella temporanea.

Oltre che alle transazioni, le variabili di tipo tabella non partecipano al logging (fino a Sql Server 2005) ed al locking; ciò ovviamente comporta un miglioramento delle prestazioni, anche se al prezzo di non poter usare queste caratteristiche.

Le operazioni che coinvolgono variabili di tabella non possono essere oggetto di parallelismo da parte del motore di Sql Server, laddove invece le tabelle temporanee sì; questo tra l’altro è un ulteriore motivo per il quale è meglio usare le variabili di tipo tabella per quantità ridotte di dati (le famose 100 – o 1000 – righe di cui sopra).

Da un punto di visto operativo, le tabelle temporanee possono essere create direttamente da un’istruzione SELECT INTO, laddove una variabile di tabella invece richiede di essere esplicitamente definita prima di poter essere usata. Questa differenza si fa sentire specialmente nel caso di query ad-hoc oppure eseguendo una query (ad esempio contenuta in una stored procedure) su una tabella che abbia subito modifiche nella sua definizione, dal momento che la tabelle temporanee create con la SELECT INTO non richiedono di essere allineate con eventuali variazioni apportate alla tabella di partenza, come invece andrebbe fatto nel caso della variabili di tipo tabella.

StackOverflow : What’s the difference between a temp table and table variable in SQL Server?

Sql Server Planet : Yet Another Temp Tables Vs Table Variables Article

Temporary tables in SQL Server vs. table variables

SQL Server Storage Engine Blog – TempDB: Table variable vs local temporary table

INF: Domande frequenti – SQL Server 2000 – Variabili di tabella

Table Data Type (Transact-SQL)

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*