Redgate Hub (Italiano)

Le tabelle temporanee sono proprio questo. Sono utilizzati più spesso per fornire lo spazio di lavoro per i risultati intermedi durante lelaborazione dei dati allinterno di un batch o di una procedura. Sono anche usati per passare una tabella da una funzione con valori di tabella, per passare dati basati su tabella tra stored procedure o, più recentemente sotto forma di parametri con valori di tabella, per inviare intere tabelle di sola lettura dalle applicazioni alle routine di SQL Server o passare tabelle temporanee di sola lettura come parametri. Una volta terminato il loro utilizzo, vengono eliminati automaticamente.

Prima di entrare troppo in profondità nella tecnologia, ti consiglio di utilizzare le variabili di tabella ove possibile. Sono facili e SQL Server fa il lavoro per te. Inoltre tendono a causare meno problemi a un sistema OLTP che lavora sodo. Solo occasionalmente, potrebbe essere necessario ottimizzarli per ottenere buone prestazioni da loro nei join, ma lo spiegherò tra un momento, Tuttavia, se stai eseguendo elaborazioni più complesse su dati temporanei o è probabile che tu usi più che ragionevolmente piccolo quantità di dati al loro interno, quindi le tabelle temporanee locali potrebbero essere una scelta migliore.

Variabili di tabella

Le variabili di tabella vengono utilizzate nellambito della routine o del batch in cui si trovano definiti e sono stati originariamente creati per rendere possibili funzioni con valori di tabella. Tuttavia, sono utili per molti degli usi a cui è stato destinato il tradizionale tavolo temporaneo. Si comportano come le altre variabili nelle loro regole di scoping. Una volta fuori ambito, vengono smaltiti. Questi sono molto più facili da lavorare e abbastanza sicuri e attivano anche meno ricompilazioni nelle routine in cui vengono utilizzati rispetto a quando si utilizzano tabelle temporanee. Le variabili di tabella richiedono meno risorse di blocco poiché sono “private” per il processo che le ha create. I rollback delle transazioni non li influenzano perché le variabili di tabella hanno un ambito limitato e non fanno parte del database persistente, quindi sono utili per creare o archiviare dati che dovrebbero sopravvivere ai rollback come le voci di registro. Lo svantaggio delle variabili di tabella è che vengono spesso eliminate prima che tu possa indagare sul loro contenuto per il debug o usarle per provare diverse espressioni SQL in modo interattivo.

Se la tua applicazione è conservativa e i tuoi volumi di dati ti illuminano Non vorrò mai nientaltro. Tuttavia, puoi incontrare problemi. Una difficoltà è che è possibile fare riferimento alle variabili di tabella solo nel loro ambito locale, quindi non è possibile elaborarle utilizzando SQL dinamico come si farebbe con una tabella temporanea o un parametro con valori di tabella. Questo perché non puoi fare riferimento a una variabile di tabella definita esternamente allinterno di SQL dinamico che poi esegui tramite listruzione EXEC o la procedura memorizzata sp_ExecuteSQL perché lSQL dinamico viene eseguito al di fuori dellambito della variabile di tabella. È possibile, ovviamente, creare e quindi utilizzare la variabile di tabella allinterno dellSQL dinamico perché la variabile di tabella sarebbe nellambito. Tuttavia, una volta eseguito lSQL dinamico, non ci sarebbe alcuna variabile di tabella

Ci sono anche alcune anomalie di cui essere consapevoli. Non è possibile, ad esempio, modificare la definizione della tabella dopo listruzione DECLARE iniziale. In SQL Server 2000, una variabile di tabella non può essere la destinazione di unistruzione SELECT INTO o di unistruzione INSERT EXEC (ora corretta); Non è possibile chiamare funzioni definite dallutente da vincoli CHECK, valori DEFAULT e colonne calcolate nella variabile di tabella. Gli unici vincoli consentiti oltre i vincoli CHECK sono PRIMARY KEY, UNIQUE KEY e NULL / NOT NULL

I problemi più complicati, tuttavia, derivano dallaumento delle dimensioni delle tabelle, perché, prima di SQL Server 2016 , non era possibile dichiarare un indice in modo esplicito e gli indici che imponevano i vincoli UNIQUE e PRIMARY KEY non disponevano di indici di distribuzione mantenuti su di essi. Ora puoi creare determinati tipi di indice in linea con la definizione della tabella, ma le statistiche di distribuzione non vengono ancora mantenute su di essi. Query Optimiser presuppone che nella tabella sia presente una sola riga. Inoltre, non è possibile generare piani di query parallela per unespressione SQL che sta modificando il contenuto della tabella. Per aggirare parzialmente la restrizione dellindice, puoi usare i vincoli per fare la stessa cosa. Il più essenziale è il vincolo della chiave primaria che consente di imporre un indice cluster, ma i vincoli univoci sono utili per le prestazioni. Query Optimizer le utilizzerà volentieri se sono disponibili.

Il problema più grande con le variabili di tabella è che le statistiche non vengono mantenute sulle colonne. Ciò significa che lottimizzatore di query deve fare unipotesi sulla dimensione e sulla distribuzione dei dati e se sbaglia, allora vedrai scarse prestazioni sui join: se ciò accade, cè poco che puoi fare altro piuttosto che ripristinare le classiche tabelle temporanee locali. A partire da SQL Server 2019, Microsoft ha introdotto una nuova funzionalità chiamata Compilazione differita variabile di tabella che risolve questo problema. Per saperne di più, leggi questo articolo di Greg Larsen.

Se non stai utilizzando SQL Server 2019, una cosa che puoi provare è aggiungere OPTION (RECOMPILE) allistruzione che implica lunione della variabile di tabella con altre tabelle. In questo modo, SQL Server sarà in grado di rilevare il numero di righe durante la ricompilazione perché le righe saranno già state popolate. Ciò non risolve completamente il problema poiché lottimizzatore non ha ancora statistiche di distribuzione e può, di solito quando la distribuzione è distorta, produrre un cattivo piano. In questa demo, il join è stato ridotto nel tempo di tre quarti semplicemente aggiungendo OPTION (RECOMPILE)

Ora se puoi rendere unico ciò che va nelle tabelle, puoi quindi utilizzare un vincolo di chiave primaria su queste tavoli. Ciò ha consentito allottimizzatore di utilizzare una ricerca di indice cluster invece di una scansione della tabella e il tempo di esecuzione era troppo rapido per essere misurato

Inizia con le variabili della tabella, ma torna a utilizzare le tabelle temporanee locali se riscontri problemi di prestazioni. Alcune persone sono abbastanza audaci da dare consigli in termini di numero di righe in una tabella e ho visto 100 o 1000 offerte come massimo; ma ho visto variabili di tabella molto più grandi funzionare perfettamente in modo soddisfacente nel tempo e quelle molto più piccole danno problemi. Tuttavia, in tabelle più piccole, il problema è meno rilevabile!

Parametri con valori di tabella

Il parametro con valori di tabella (TVP) è un tipo speciale di variabile di tabella che ne estende luso. Quando le variabili di tabella vengono passate come parametri, la tabella viene materializzata nel database di sistema TempDB come una variabile di tabella e passata per riferimento, un puntatore alla tabella in TempDB.

Da allora sono stati utilizzati parametri con valori di tabella SQL Server 2008 per inviare diverse righe di dati a una routine Transact-SQL o a un batch tramite sp_ExecuteSQL .. Il loro valore particolare per il programmatore è che possono essere utilizzate allinterno del codice TSQL come così come nellapplicazione client, quindi sono utili per inviare tabelle client al server. Da TSQL, è possibile dichiarare variabili con valori di tabella, inserire dati in esse e passare queste variabili come parametri con valori di tabella a procedure e funzioni memorizzate. La loro utilità più generale è limitata dal fatto che vengono passate solo in sola lettura. Non puoi eseguire UPDATE, DELETE o INSERT istruzioni con valori di tabella parametro nel corpo di una routine.

È necessario creare un tipo di tabella definito dallutente e definire una struttura di tabella per utilizzarli. Ecco un semplice esempio del loro utilizzo in TSQL

Come con le variabili di tabella, il parametro con valori di tabella cessa di esistere una volta che è fuori ambito, ma la definizione del tipo rimane finché non viene esplicitamente eliminata.Come le variabili di tabella, non acquisiscono blocchi quando i dati vengono popolati da un client e le statistiche non vengono mantenute su colonne di parametri con valori di tabella. Non è possibile utilizzare un parametro con valori di tabella come destinazione di unistruzione SELECT INTO o INSERT EXEC. Come ci si aspetterebbe, un parametro con valori di tabella può trovarsi nella FROM clausola di SELECT INTO o nella INSERT EXEC stringa o procedura memorizzata.

Il TVP risolve il problema comune di voler passare una variabile locale a SQL dinamico che viene quindi eseguita da un sp_ExecuteSQL. È scarsamente documentato da Microsoft, quindi ti mostrerò un esempio funzionante per iniziare

Prima di passare alla descrizione delle tabelle temporanee più tradizionali e del loro utilizzo, avremo bisogno di approfondire il luogo in cui si tengono tavoli temporanei. TempDB.

TempDB

Le tabelle temporanee e le variabili di tabella vengono create nel database TempDB, che in realtà è solo un altro database con un semplice ripristino: con TempDB, viene eseguita solo una registrazione “minima” sufficiente per consentire il rollback e altre sottigliezze ACID. La differenza speciale di TempDB è che tutti gli oggetti come le tabelle vengono cancellati allavvio. Poiché TempDB utilizza sempre il modello di recupero semplice, la transazione completata viene cancellata dal registro di log nel successivo checkpoint TempDB e vengono conservate solo le transazioni attive. Tutto ciò significa che le tabelle temporanee si comportano come qualsiasi altro tipo di tabella di base in quanto vengono registrate e memorizzate proprio come loro. In pratica, è probabile che le tabelle temporanee rimangano memorizzate nella cache, ma solo se vengono utilizzate di frequente: come con una tabella di base. TempDB gestisce un sistema chiamato riutilizzo di oggetti temporanei, che memorizzerà nella cache una parte degli oggetti temporanei con il piano, se cè memoria sufficiente. Ciò può spiegare la leggenda secondo cui gli oggetti temporanei esistono solo in memoria. La verità come sempre è “dipende …”.

Molte altre cose vanno avanti in TempDB: il motore di database può usarlo per posizionare tabelle di lavoro per i controlli DBCC, per creare o ricostruire indici, cursori, per esempio. Le tabelle intermedie nelle query descritte come “hash”, “sorts” e “spool” vengono materializzate in TempDB, ad esempio, insieme a quelle richieste per diverse operazioni “fisiche” nellesecuzione di istruzioni SQL. Viene anche utilizzato come archivio delle versioni per lisolamento di snapshot, MARS (Multiple Active Results Sets), trigger e creazione di indici in linea.

Poiché le tabelle temporanee sono archiviate proprio come le tabelle di base, ce ne sono uno o due cose di cui devi stare attento. Ad esempio, è necessario disporre dellautorizzazione CREATE TABLE in TempDB per creare una tabella normale. Per evitare problemi, questo viene assegnato per impostazione predefinita al ruolo DBO (proprietario database), ma potrebbe essere necessario farlo esplicitamente per gli utenti a cui non è stato assegnato il ruolo DBO. Tutti gli utenti hanno i permessi per creare tabelle temporanee locali o globali in TempDB perché questo è assegnato loro tramite il GUEST contesto di sicurezza dellutente.

La classica tabella temporanea è disponibile due varianti, la tabella temporanea globale o condivisibile, preceduta da “##” e la tabella temporanea locale, il cui nome è preceduto da “#”. Le tabelle temporanee locali sono meno simili alle tabelle normali rispetto alle tabelle temporanee globali: Tu non è in grado di creare visualizzazioni su di essi o di associarvi a trigger. È un po complicato capire quale processo, sessione o procedura li ha creati. Ti daremo un po di aiuto in seguito. Ancora più importante, sono più sicuri di una tabella temporanea globale in quanto solo il processo proprietario può vederla.

Unaltra stranezza della tabella temporanea locale (e della procedura memorizzata temporanea locale) è che ha un nome diverso nei metadati a quello che dai nella tua routine o batch. Se la stessa routine viene eseguita simultaneamente da più processi, il Motore di database deve essere in grado di distinguere tra le tabelle temporanee locali con lo stesso nome create dai diversi processi. Lo fa aggiungendo una stringa numerica a ogni nome di tabella temporanea locale riempito a sinistra da caratteri di sottolineatura. Sebbene tu specifichi il nome breve come #MyTempTable, ciò che è effettivamente memorizzato in TempDB è costituito dal nome della tabella specificato nellistruzione CREATE TABLE e il suffisso. A causa di questo suffisso, i nomi delle tabelle temporanee locali devono contenere un massimo di 116 caratteri.

Se sei interessato a vedere cosa sta succedendo, puoi visualizzare le tabelle in TempDB proprio come faresti con qualsiasi altro tavolo. Puoi anche utilizzare sp_help per lavorare su tabelle temporanee solo se le richiami da TempDB.

oppure puoi trovarli nelle viste di sistema di TempDB senza cambiare database.

Oppure lo schema delle informazioni

Ancora meglio, puoi scoprire quale processo, e utente, sta trattenendo enormi tabelle temporanee in TempDB e si rifiuta di rinunciare allo spazio

Non è possibile usare tipi di dati definiti dallutente nelle tabelle temporanee a meno che i tipi di dati non esistano in TempDB; cioè, a meno che i tipi di dati non siano stati creati esplicitamente

Tabelle utente in TempDB

Nelluso normale, creerai tabelle temporanee o variabili di tabella senza pensarci troppo a fondo. Tuttavia, è interessante, tuttavia, che TempDB sia disponibile per qualsiasi tipo di attività sandbox. Puoi creare tabelle di base ordinarie, viste o qualsiasi altra cosa desideri. È possibile creare schemi, stored procedure e così via. È improbabile che tu voglia farlo, ma è certamente possibile poiché TempDB è solo un altro database. Ho appena dovuto riavviare il mio SQL Server di sviluppo dopo averlo dimostrato a me stesso installando AdventureWorks su di esso. Ciò significa che è possibile creare una tabella di base in TempDB, una sorta di ..er … tabella permanente temporanea. A differenza della tabella temporanea globale, su di essa dovresti fare tutte le tue pulizie: sei da solo. Lo stesso vale per le routine. Il vantaggio di fare questo è che qualsiasi elaborazione che fai utilizza il semplice ripristino di TempDB in modo che, se non riesci a pulire, SQL Server funge da madre al prossimo avvio: anche se questo potrebbe essere un tempo molto lungo. La fase successiva consiste nellavere quella che chiamo una tabella “temporanea persistente”. In questa tabella, i dati stessi sono volatili al riavvio del server, ma la tabella stessa persiste. Probabilmente il modo più comune per creare una tabella temporanea persistente è ricreare allavvio una tabella temporanea globale. Questa operazione può essere eseguita automaticamente quando tutti i database vengono ripristinati e il messaggio “Ripristino completato” viene registrato. Anche se si tratta di un “temporaneo globale”, non viene eliminato quando tutte le connessioni che lo utilizzano sono scomparse, perché il processo che lo esegue non scompare mai. Probabilmente, è meglio creare questo tipo di tabella di lavoro nel database che lo utilizza, tuttavia, se si utilizza il ripristino completo, il lavoro temporaneo rimarrà nel registro. È possibile, ovviamente, creare un normale tabella in TempDB. È possibile creare queste tabelle “persistenti” allavvio definendo una procedura memorizzata nel master che crei la tabella temporanea globale

Perché utilizzare questo tipo di tabella ibrida? Esistono, ad esempio, un numero di tecniche per il passaggio di tabelle tra procedure tramite tabelle “persistenti” in modo multiprocesso sicuro, in modo da eseguire una serie di elaborazioni sui dati. Queste sono riferite a tabelle con chiave di processo (vedere “Come condividere i dati tra procedure archiviate : Tavolo con chiave di processo di Erland Sommarskog ). Inizialmente solleveranno le sopracciglia di qualsiasi DBA esperto, ma sono una soluzione efficace e sicura a un problema perenne, quando vengono eseguiti correttamente.

Oltre alle tabelle temporanee, ci sono anche una serie di tipi di tabelle che non derivano direttamente da tabelle di base, come tabelle “false” e tabelle derivate: alcune di queste sono così fugaci che è meglio pensarle come effimere piuttosto che temporanee. Il CTE utilizza tabelle temporanee che sono “inline” o “derivate” e non vengono materializzate. BOL si riferisce a loro come “set di risultati denominati temporanei”. Esistono solo nellambito dellespressione. In una CTE, hanno il vantaggio rispetto alle tabelle derivate in quanto sono accessibili più di una volta.

Tabella temporanea locale

Con la tabella temporanea locale (nomi che iniziano con #), ciò che accade sotto il cofano è sorprendentemente simile alle variabili di tabella. Come con le variabili di tabella, le tabelle temporanee locali sono private per il processo che le ha create. Non possono quindi essere utilizzati nelle viste e non è possibile associare trigger ad essi.

Sono più maneggevoli delle variabili di tabella se ti piace usare SELECT INTO per crearle, ma sono leggermente diffidente nellusare SELECT INTO in un sistema che probabilmente richiederà modifiche, preferirei di gran lunga creare le mie tabelle temporanee esplicitamente, insieme a tutti i vincoli necessari.

Non si può facilmente dire quale sessione o procedura ha creato queste tabelle. Questo perché, se la stessa stored procedure viene eseguita simultaneamente da più processi, il Motore di database deve essere in grado di distinguere le stesse tabelle create dai diversi processi. Il Motore di database esegue questa operazione aggiungendo internamente un suffisso numerico con riempimento a sinistra a ciascun nome di tabella temporanea locale. Il nome completo di una tabella temporanea archiviata nella vista sys.objects in TempDB è costituito dal nome della tabella specificato nellistruzione CREATE TABLE e dal suffisso numerico generato dal sistema. Per consentire il suffisso, il nome della tabella specificato per un nome temporaneo locale deve contenere meno di 116 caratteri.

Si ottengono pulizie con le tabelle temporanee locali; vengono automaticamente eliminati quando escono dallambito, a meno che non vengano eliminati esplicitamente utilizzando DROP TABLE. Il loro ambito è più ampio di una variabile di tabella, quindi non hai problemi a farvi riferimento allinterno di batch o in SQL dinamico. Le tabelle temporanee locali vengono eliminate automaticamente alla fine della sessione o procedura corrente. Lasciarlo cadere alla fine della procedura che lo ha creato può causare grattacapi: una tabella temporanea locale che viene creata allinterno di una stored procedure o di una sessione viene eliminata quando è terminata, quindi non può essere referenziata dal processo che ha chiamato la stored procedure che ha creato la tabella. Può, tuttavia, essere referenziato da qualsiasi stored procedure nidificata eseguita dalla stored procedure che ha creato la tabella. Se la procedura annidata fa riferimento a una tabella temporanea e in quel momento esistono due tabelle temporanee con lo stesso nome, su quale tabella è stata risolta la query?

Come curiosità, puoi anche creare stored procedure temporanee locali con lo stesso ambito e durata di una tabella temporanea locale. Non puoi fare lo stesso per altre routine.

Tabelle temporanee globali.

Come le tabelle temporanee locali, le tabelle temporanee globali (iniziano con ##) vengono automaticamente eliminate quando la sessione che ha creato la tabella termina: Tuttavia, perché le tabelle globali non sono privati per il processo che lo ha creato, devono persistere da allora in poi fino allultima istruzione Transact-SQL che faceva attivamente riferimento alla tabella nel momento in cui la sessione di creazione è terminata ha terminato lesecuzione e i blocchi vengono eliminati. Chiunque abbia accesso a TempDB nel momento in cui esistono queste tabelle temporanee globali può eseguire query, modificare o eliminare direttamente questi oggetti temporanei.

Puoi associare regole, impostazioni predefinite e indici a tabelle temporanee, ma non puoi creare visualizzazioni su tabelle temporanee o associare trigger ad esse. È possibile utilizzare un tipo di dati definito dallutente durante la creazione di una tabella temporanea solo se il tipo di dati esiste in TempDB

Le procedure memorizzate possono fare riferimento a tabelle temporanee create durante la sessione corrente. Allinterno di una stored procedure, non è possibile creare una tabella temporanea, rilasciarla e quindi creare una nuova tabella temporanea con lo stesso nome.

Anche se questo funziona …

… questo non funziona ” t

È possibile, utilizzando tabelle temporanee locali, forzare involontariamente la ricompilazione della stored procedure ogni volta che viene utilizzata. Questo non va bene perché è improbabile che la stored procedure funzioni bene. Per evitare la ricompilazione, evitare di fare riferimento a una tabella temporanea creata in una procedura memorizzata chiamante o chiamata: se non è possibile farlo, inserire il riferimento in una stringa che viene quindi eseguita utilizzando EXECUTE istruzione o sp_ExecuteSQL procedura memorizzata. Inoltre, assicurarsi che la tabella temporanea venga creata nella stored procedure o nel trigger prima che venga fatto riferimento e che venga eliminata dopo questi riferimenti.Non creare una tabella temporanea allinterno di unistruzione di controllo del flusso come IF... ELSE o WHILE.

È consentito creare procedure archiviate temporanee globali, ma devo ancora trovarne un utilizzo. Le funzioni temporanee globali non sono consentite.

Conclusioni

In qualsiasi parco giochi condiviso, fai molta attenzione a come muovi la mazza. Ti sarai reso conto, leggendo questo, che molte attività si svolgono in TempDB e puoi causare danni allintero SQL Server utilizzando processi a esecuzione prolungata che riempiono tabelle temporanee, qualunque siano, con quantità non necessarie di dati. In effetti, in questo articolo ti ho fornito indizi su come sconvolgere davvero, davvero, il tuo DBA con un uso sconsiderato di quella preziosa risorsa condivisa, il TempDB. (Ai vecchi tempi prima dellS2005, usare SELECT INTO con un enorme tavolo era la grande arma a V (Vergeltungswaffe)

Sono sempre diffidente nel fornire over- consiglio generalizzato, ma preferisco sempre che i miei database utilizzino variabili di tabella e TVP laddove possibile. Richiedono meno risorse ed è meno probabile che tu le mantenga quando hai finito. Mi piace usarle al massimo , con controlli e vincoli di colonne e tabelle. Potresti trovare momenti in cui si esauriscono, soprattutto quando le dimensioni delle tabelle diventano più grandi. In casi come questo, o in cui non è pratico utilizzare le variabili di tabella a causa del loro ambito limitato, Userò tabelle temporanee locali. Ci vogliono molte labbra contratte e scuotere la testa prima di accettare una tabella temporanea globale o una tabella temporanea persistente. Hanno pochi usi validi e perfettamente ragionevoli, ma fanno affidamento sul programmatore per fare le pulizie necessarie