SQL Server temp tabelle sono un tipo speciale di tabelle che sono scritti nel database TempDB e di agire come un normale tabelle, offrendo un posto di lavoro adatto per intermedi di elaborazione dei dati, prima di salvare il risultato in un normale tavolo, come si può vivere solo per l’età della connessione al database.,
Le tabelle temp SQL possono essere utilizzate per migliorare le prestazioni delle stored procedure riducendo il tempo di transazione, consentendo di preparare i record che verranno modificati nella tabella temp di SQL Server, quindi aprire una transazione ed eseguire le modifiche.
Esistono quattro tipi principali per le tabelle temp SQL:
-
una tabella temp SQL Server locale, che viene denominata a partire da un simbolo # (ad esempio #TempShipments), che può essere referenziata solo dalla sessione di database corrente e scartata dalla sua disconnessione,
-
una tabella temp SQL globale, che viene denominata a partire da # # (ad esempio, ##TempShipments), che può essere fatto riferimento da parte di qualsiasi processo nel database corrente ed eliminate quando il database originale sessione creato una tabella temp scollegato o fino a quando l’ultima istruzione che fa riferimento la tabella temp ha smesso di usarlo, come chiunque abbia accesso al sistema di database TempDB quando globale SQL temp tabella viene creata sarà in grado di utilizzare tale tabella,
-
un SQL Server Persistente tabella temporanea che è denominato a partire da un database TempDB prefisso come TempDB.DBO.TempShipments
-
e una variabile di tabella che inizia con un prefisso @ (ad esempio, @TempShipments)
Il motore di database di SQL Server può distinguere tra le stesse tabelle temporanee SQL create durante l’esecuzione della stessa stored procedure molte volte contemporaneamente aggiungendo un suffisso numerico generato dal sistema al nome della tabella temporanea di SQL Server. Questo è il motivo per cui il nome della tabella temporanea SQL locale non può superare i 116 caratteri.,
Sebbene sia le tabelle temp di SQL Server che le variabili di tabella siano memorizzate nel database TempDB, ci sono molte differenze tra loro come:
-
Le tabelle temp SQL vengono create usando CREATE TABLE T-SQL statement, ma le variabili di tabella vengono create usando DECLARE @name Table T-SQL statement.
-
È possibile MODIFICARE le tabelle temporanee di SQL Server dopo averlo creato, ma le variabili di tabella non supportano alcuna istruzione DDL come l’istruzione ALTER.
-
Le tabelle temporanee SQL non possono essere utilizzate nelle funzioni definite dall’utente, ma le variabili di tabella possono esserlo.,
-
Le tabelle temporanee di SQL Server onorano le transazioni esplicite definite dall’utente, ma le variabili di tabella non possono partecipare a tali transazioni.
-
Le tabelle temporanee SQL supportano l’aggiunta di indici cluster e non cluster dopo la creazione della tabella temporanea di SQL Server e implicitamente definendo il vincolo della chiave primaria o il vincolo della chiave univoco durante la creazione delle tabelle, ma le variabili della tabella supportano solo l’aggiunta di tali indici implicitamente definendo il vincolo della chiave primaria,
-
Le tabelle temporanee SQL possono essere eliminate esplicitamente, ma le variabili di tabella non possono essere eliminate esplicitamente, tenendo conto che entrambi i tipi vengono eliminati automaticamente quando la sessione in cui vengono creati viene disconnessa.
-
Le tabelle temp di SQL Server possono essere tabelle temporali locali a livello del batch o della stored procedure in cui la tabella ha dichiarato o tabelle temporali globali in cui può essere chiamata al di fuori dell’ambito batch o stored procedure, ma le variabili della tabella possono essere chiamate solo all’interno del batch o della stored procedure in,
In aggiunta a ciò, le statistiche a livello di colonna di SQL Server vengono generate automaticamente rispetto alle tabelle temp SQL, aiutando SQL Server Query Optimizer a generare il miglior piano di esecuzione, ottenendo le migliori prestazioni durante l’interrogazione di tale tabella temp SQL Server. Ma dovresti prendere in considerazione che la modifica delle tabelle temporanee SQL molte volte nel tuo codice può portare a statistiche obsolete. Ciò richiederebbe l’aggiornamento manuale per queste statistiche o l’abilitazione di Trace Flag 2371., In questo articolo, vedremo come possiamo beneficiare della possibilità di aggiungere indici cluster e non cluster nelle tabelle temporanee di SQL Server.
La definizione di CHIAVE PRIMARIA e vincoli CHIAVE UNIVOCI durante la creazione di tabelle temp SQL consentirà a SQL Server Query Optimizer di essere sempre in grado di utilizzare questi indici. Anche così, questi indici impediscono l’inserimento di valori non univoci a queste colonne, che non è il caso migliore in tutti gli scenari, che potrebbero richiedere valori non univoci. In questo caso, è meglio definire esplicitamente un indice cluster o non cluster che potrebbe essere configurato come un indice non univoco., L’aggiunta di indici alle tabelle temp SQL migliorerà le sue prestazioni se l’indice viene scelto correttamente, altrimenti può causare un degrado delle prestazioni. Inoltre, non tutte le tabelle temp di SQL Server devono aggiungere indici, in quanto dipende da molte cose come il modo in cui verrà chiamata questa tabella temp SQL, unita ad altre tabelle enormi o se farà parte di una stored procedure complessa.,
Iniziamo la nostra demo, in cui testeremo le prestazioni di riempimento e recupero dei dati da tabelle temporanee di SQL Server che contengono record 100k, senza alcun indice, con un indice non cluster e con un indice cluster. Ci concentreremo nel controllare il tempo consumato da ogni caso e il piano di esecuzione generato. Per valutare il tempo consumato, dichiareremo la variabile @StartTime prima di ogni esecuzione, impostiamo il suo valore su GETDATE () e alla fine di ogni esecuzione, stamperemo la differenza di data (in ms) tra l’ora corrente e l’ora di inizio.,
Il seguente script crea il già menzionato tre SQL tabelle temp; temp tabella senza un indice, la tabella temporanea con un indice non cluster e temp tabella con indice cluster e riempire con 100k record dal CountryInfo tabella di prova per poi recuperare questi record dalle tabelle:
Esecuzione script precedente, il risultato ci mostra che, nel nostro caso, l’aggiunta di un indice non cluster è peggio che avere un tavolo senza indice 1.,e ora, nel nostro caso, come nella seguente tempistica confronto in ms:
Verifica il generati piano di esecuzione utilizzando il ApexSQL Piano di applicazione dopo l’esecuzione, si vede che non hanno unisce con enorme tabelle o query complesse, il recupero dei dati da tre tavoli, pari al consumo di risorse (1%) e si differenziano per l’operatore che viene utilizzato per recuperare i dati; Tabella Scansione in caso di temp tabella senza indice, Indice di ricerca in caso di temp tabella con indice non cluster e Cluster di ricerca Indice in caso di temp tabella con indice cluster.,
Inoltre, è possibile derivare dall’esecuzione del piano, che la tabella con indice non cluster ha preso il più grande tempo (1063 ms) e le risorse (47% del totale di esecuzione) durante l’inserimento di una tabella del processo di fronte alla tavola con l’indice cluster di inserimento che ha avuto meno tempo (827 ms) e risorse (32 % del totale di esecuzione):
Nello script precedente abbiamo creato un indice non cluster dopo il riempimento della tabella temporanea e l’indice cluster prima di riempire la tabella temporanea., Ma è diverso quando creiamo l’indice prima o dopo aver riempito la tabella temporanea?, Per rispondere a questa domanda proviamo a eseguire il seguente test che permetterà di controllare il consumo in tutti i casi, l’aggiunta di un indice non cluster prima di riempire la tabella temporanea, l’aggiunta di un indice non cluster dopo il riempimento della tabella temporanea, l’aggiunta di un indice cluster prima di riempire la tabella temporanea e l’aggiunta di un indice cluster dopo il riempimento della tabella temp:
è chiaro Che per il risultato generato dall’esecuzione dello script precedente che è meglio per creare l’indice non cluster dopo il riempimento della tabella, che è dell ‘ 1,2% più veloce, e creare l’indice cluster prima di riempire la tabella, che è 2.,5% più veloce, a causa del meccanismo che viene utilizzato per riempire le tabelle e creare gli indici:
Controllando il piano di esecuzione, il risultato ci mostrerà che la creazione dell’indice cluster prima dell’inserimento consuma il 15,7% dell’esecuzione complessiva, dove la creazione dopo l’inserimento consumerà il 22% dell’esecuzione complessiva., D’altra parte, la creazione di indici non cluster, dopo l’inserimento consuma il 23% delle risorse rispetto al 25% per cento che si è consumato con la creazione prima che il processo di inserimento:
Conclusione
In SQL Server, SQL tabelle temporanee, che vengono memorizzati nel database TempDB, sono ampiamente utilizzati per fornire un luogo adatto per la intermedi di elaborazione dei dati, prima di salvare il risultato in una tabella di base., Ha anche usato per abbreviare la durata delle transazioni di lunga durata con il blocco minimo della tabella di base prendendo i dati, elaborandoli e infine aprendo una transazione per eseguire la modifica sulla tabella di base. Questo approccio è applicabile per aggiungere indici non cluster e cluster alle tabelle temp di SQL Server, che possono migliorare le prestazioni del recupero dei dati da queste tabelle se gli indici vengono scelti correttamente.,
- Author
- Recent Posts
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.,
Inoltre, sta contribuendo con i suoi suggerimenti SQL in molti blog.
Visualizza tutti i messaggi di Ahmad Yaseen
- Come per monitorare Azure Dati di Fabbrica – il 15 gennaio 2021
- Uso del Controllo del codice Sorgente in Azzurro i Dati di Fabbrica – gennaio 12, 2021
- Utilizzo di Modelli di Azure Dati di Fabbrica – 8 gennaio 2021