Usare le variabili tabella per eliminare l'uso dei cursori

Nel lavoro quotidiano dello sviluppatore T-SQL l'uso di tabelle temporanee e cursori è spesso necessario per tirarsi fuori dagli impicci. Ma l'uso eccessivo di queste tecniche non è consigliato dal punto di vista delle perfomance perché sia i cursori che le tabelle temporanee richiedono un extra-lavoro a SQL Server. Proviamo a vedere i passi tipici dell'utilizzo di una tabella temporanea:

1. Creiamo la tabella temporanea
2. Inseriamo i nostri dati
3. Selezioniamo i dati dalla tabella temporanea, magari con un JOIN ad una tabella fisica del database, con conseguente LOCK (blocco) dell'intero database tempdb fino al completamento della transazione
4. Eliminiamo la tabella temporanea

I passi elencati dimostrano chiaramente l'intensa attività I/O fatta da SQL Server durante l'uso di una tabella temporanea, senza parlare del blocco del database tempdb e dei potenziali conflitti che potrebbero verificarsi. Questi problemi sono facilmente identificabili anche attraverso l'uso dei cursori.

La soluzione

Una possibile strada per risolvere questi inconvenienti ci viene da un nuovo data type (meglio dire table type) introdotto con la versione 2000 di SQL Server. Il table type tabella è sostanzialmente il clone di una tabella normale, ma a differenza di questa è una variabile locale che risiede totalmente in memoria e quindi non ha problemi di conflitti, locking o attività I/O.La variabile tabella viene dichiarata all'interno di stored procedures, funzioni o batch ed è automaticamente eliminata (dalla memoria) alla fine di ogni stored procedures, funzione o batch. Grazie a questo nuovo tipo dato SQL Server 2000 introduce qualcosa di simile all'array nella programmazione T-SQL (vedi il codice).

Usare i table type è veramente molto semplice, nell'esempio riportato qui sotto creo una variabile tabella dal nome @authors_first_letter_s in cui andrò ad inserire tutti gli autori il cui cognome inizia con la 'S'. Dopo posso usare la variabile tabella @authors_first_letter_s al pari di una tabella vera e propria, ad esempio per fare una JOIN con la tabella authors, utilizzarla all'inteno di subquery o come una tabella derivata, ecc.Se possedete SQL Server 2000 vi consiglio di sfruttare al massimo le variabili tabella usandole ove necessario al posto delle tabelle temporanee.

Codice di esempio:

Use Pubs
GO

/*
Dichiaro la variabile @authors_first_letter_s e ne definisco 
la struttura nello stesso modo di quando creo una tabella.
*/
DECLARE @authors_first_letter_s TABLE (
  au_id varchar (40) NOT NULL,
  au_lname varchar (40),
  au_fname varchar (20)
)

/*
Inserisco nella variabile tabella che ho appena dichiarato 
gli autori il cui cognome inzia con la S
*/
INSERT INTO @authors_first_letter_s
SELECT au_id, au_lname, au_fname
FROM authors WHERE LEFT(au_lname,1)='S'

/*
Ora posso selezionare il contenuto della mia variabile come 
se fosse una semplice tabella
*/
SELECT au_id FROM @authors_first_letter_s

/*
E fare tutto quello che pare, ad esempio una JOIN alla tabella authors!!!!
*/
SELECT * FROM authors a INNER JOIN @authors_first_letter_s b
ON a.au_id=b.au_id
GO

/*
Proviamo a simulare un array in SQL Server 2000: dichiariamo 
la variabile tabella
*/
DECLARE @array TABLE (
  indice INTEGER IDENTITY(1,1),
  valore VARCHAR(255) NULL
)
DECLARE @i INTEGER

/*
Mettiamo dei valori nel nostro array
*/
SET @i = 0
WHILE @i < 255
BEGIN
  INSERT INTO @array VALUES ( REPLICATE('*',@i+1) )
  SET @i = @i + 1
END

/*
Visualizziamo i valori contenuti nell'array
*/
SELECT valore FROM @array

/*
Cerchiamo i valori contenuti nella prima e nell'ultima posizione dell'array
*/
DECLARE @max INTEGER, @min INTEGER
SELECT @max=MAX(indice) FROM @array
SELECT @min=MIN(indice) FROM @array
SELECT valore FROM @array WHERE indice = @min OR indice = @max