Qual è la differenza tra una tabella temporanea e una variabile di tabella in SQL Server?


404

Questa sembra essere un'area con parecchi miti e punti di vista contrastanti.

Quindi qual è la differenza tra una variabile di tabella e una tabella temporanea locale in SQL Server?

+4

Domanda correlata su Stack Overflow: [Quando dovrei utilizzare una tabella variabile rispetto a una tabella temporanea in SQL Server?] (Http://stackoverflow.com/q/11857789/73226) 07 lug. 162016-07-07 04:30:51

618

Contenuto

Contents

Avvertimento

Questa risposta illustra le variabili di tabella "classiche" introdotte in SQL Server 2000. SQL Server 2014 nella memoria OLTP introduce tipi di tabella ottimizzati per la memoria.Le istanze variabili di tabella di queste sono diverse sotto molti aspetti rispetto a quelle discusse in seguito!(more details).

Posizione di archiviazione

Nessuna differenza.Entrambi sono memorizzati intempdb.

Ho visto che suggeriva che per le variabili di tabella questo non è sempre il caso, ma questo può essere verificato dal seguente

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Risultati di esempio (mostrando posizione intempdble 2 righe sono memorizzate)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Posizione logica

@table_variablescomportarsi di più come se facessero parte del database corrente di#temptavoli fanno.Per le variabili di tabella (dal 2005) le regole di confronto delle colonne, se non specificate esplicitamente, saranno quelle del database corrente, mentre per#temptabelle utilizzerà le regole di confronto predefinite ditempdb(More details).Inoltre, i tipi di dati definiti dall'utente e le raccolte XML devono essere utilizzati in tempdb#temple tabelle ma le variabili di tabella possono utilizzarle dal database corrente (Source).

SQL Server 2012 introduce database contenuti.the behavior of temporary tables in these differs(h/t Aaron)

In un database contenuto, i dati della tabella temporanea vengono raccolti nelle regole di confronto del database contenuto.

  • Tutti i metadati associati alle tabelle temporanee (ad esempio nomi di tabelle e colonne, indici e così via) saranno presenti nelle regole di confronto del catalogo.
  • I vincoli denominati non possono essere utilizzati nelle tabelle temporanee.
  • Le tabelle temporanee non possono fare riferimento a tipi definiti dall'utente, raccolte di schemi XML o funzioni definite dall'utente.

Visibilità a diversi ambiti

@table_variablesè possibile accedere solo all'interno del batch e dell'ambito in cui sono dichiarati.#temp_tablessono accessibili all'interno di lotti figlio (trigger innestati, procedura,execchiamate).#temp_tablescreato allo scopo esterno (@@NESTLEVEL=0) può estendersi anche su lotti mentre persistono fino alla fine della sessione.Né il tipo di oggetto può essere creato in un batch figlio, né l'accesso all'ambito delle chiamate, come discusso successivamente (globale##temptavolipuòessere però).

Tutta la vita

@table_variablesvengono creati implicitamente quando un batch contenente unDECLARE @.. TABLEl'istruzione viene eseguita (prima dell'esecuzione di qualsiasi codice utente in quel batch) e viene rilasciata implicitamente alla fine.

Sebbene il parser non ti permetta di provare e usare la variabile table prima delDECLAREdichiarazione la creazione implicita può essere visto sotto.

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tablesvengono creati esplicitamente quando il TSQLCREATE TABLEla dichiarazione è incontrata e può essere eliminata esplicitamente conDROP TABLEo verrà interrotto implicitamente al termine del batch (se creato in un batch figlio con@@NESTLEVEL > 0) o quando la sessione finisce diversamente.

NB: All'interno delle routine memorizzate entrambi i tipi di oggettocan be cachedpiuttosto che creare e rilasciare ripetutamente nuove tabelle.Ci sono restrizioni su quando può verificarsi questa memorizzazione nella cache, tuttavia è possibile violare per#temp_tablesma quali le restrizioni su@table_variablesprevenire comunque.Il sovraccarico di manutenzione per la cache#temptavoli èleggermentemaggiore di per le variabili di tabellaas illustrated here.

Metadati dell'oggetto

Questo è essenzialmente lo stesso per entrambi i tipi di oggetto.È memorizzato nelle tabelle di base del sistema intempdb.È più semplice da vedere per a#temptavolo tuttavia comeOBJECT_ID('tempdb..#T')può essere utilizzato per inserire le tabelle di sistema e il nome generato internamente è più strettamente correlato al nome definito inCREATE TABLEdichiarazione.Per le variabili di tabella ilobject_idla funzione non funziona e il nome interno è interamente generato dal sistema senza alcuna relazione con il nome della variabile.Di seguito viene mostrato che i metadati sono ancora presenti tuttavia digitando un nome di colonna (si spera univoco).Per le tabelle senza nomi di colonne univoci è possibile determinare object_id utilizzandoDBCC PAGEfinché non sono vuoti.

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
     WITH (FILLFACTOR = 80, 
        IGNORE_DUP_KEY = ON, 
        DATA_COMPRESSION = PAGE, 
        ALLOW_ROW_LOCKS=ON, 
        ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM  tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
        ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
        ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
        ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

Produzione

Duplicate key was ignored.

    +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| object_id |  name  | rows |   type_desc   | total_pages | used_pages | data_pages | data_compression_desc |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | PAGE         |
| 574625090 | #22401542 |  13 | LOB_DATA     |     24 |     19 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | ROW_OVERFLOW_DATA |     16 |     14 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | NONE         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

Le transazioni

Operazioni su@table_variablessono eseguiti come transazioni di sistema, indipendentemente da qualsiasi transazione utente esterna, mentre l'equivalente#temple operazioni di tabella verrebbero eseguite come parte della transazione dell'utente stessa.Per questo motivo aROLLBACKil comando influenzerà a#temptavolo ma lasciare il@table_variableintatto.

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Registrazione

Entrambi generano record di registro sutempdbregistro delle transazioni.Un malinteso comune è che questo non è il caso delle variabili di tabella, quindi uno script dimostrativo è inferiore, dichiara una variabile di tabella, aggiunge un paio di righe, quindi le aggiorna e le elimina.

Poiché la variabile di tabella viene creata e rilasciata implicitamente all'inizio e alla fine del batch, è necessario utilizzare più batch per visualizzare la registrazione completa.

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET  B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM  sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
        ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
        ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM  fn_dblog(NULL, NULL)
WHERE AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
      AS (SELECT Operation,
           Context,
           CASE
            WHEN AllocUnitId = @allocId THEN 'Table Variable'
            WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
            ELSE AllocUnitName
           END AS AllocUnitName,
           [Log Record Length]
        FROM  fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
              WHEN GROUPING(Operation) = 1 THEN 'Total'
              ELSE Operation
             END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM  T
GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER BY GROUPING(Operation),
        AllocUnitName 

ritorna

Vista dettagliata

Screenshot of results

Vista di riepilogo (include la registrazione per le tabelle implicite di caduta e di sistema)

Screenshot of results

As far as I've been able to discernle operazioni su entrambi generano quantità approssimativamente uguali di registrazione.

Mentre ilquantitàdi registrazione è molto simile una differenza importante è che i record di registro relativi a#temple tabelle non possono essere cancellate fino a quando nessuna transazione utente contenente finisce così una transazione a lungo termine che a un certo punto scrive#temple tabelle impediranno il troncamento del registrotempdbmentre le transazioni autonome generate per le variabili di tabella non lo fanno.

Le variabili di tabella non supportanoTRUNCATEquindi può essere uno svantaggio di registrazione quando il requisito è quello di rimuovere tutte le righe da una tabella (anche se per tabelle molto piccoleDELETEcan work out better anyway)

Cardinalità

Molti piani di esecuzione che coinvolgono variabili di tabella mostreranno una singola riga stimata come output da essi.Ispezionare le proprietà della variabile di tabella mostra che SQL Server ritiene che la variabile di tabella abbiazerorighe (Perché stima che 1 riga verrà emessa da una tabella a righe zero è spiegata da @Paul Whitehere).

Tuttavia i risultati mostrati nella sezione precedente mostrano un accuratorowscontaresys.partitions.Il problema è che nella maggior parte dei casi le istruzioni che fanno riferimento alle variabili di tabella vengono compilate mentre la tabella è vuota.Se la dichiarazione è (ri) compilata dopo@table_variableviene popolato quindi verrà usato per la cardinalità della tabella (ciò potrebbe accadere a causa di un esplicitorecompileo forse perché l'istruzione fa riferimento anche a un altro oggetto che causa una compilazione differita o una ricompilazione.)

DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Il piano mostra il numero di righe stimato accurato dopo la compilazione differita.

Shows accurate row count

In SQL Server 2012 SP2, viene introdotto il flag di traccia 2453.Maggiori dettagli sono in "Motore relazionale"here.

Quando questo flag di trace è abilitato, può far sì che le ricompilazioni automatiche tengano conto della cardinalità modificata, come discusso molto presto.

Nessuna statistica di colonna

Avere una cardinalità di tabella più accurata non significa che il conteggio delle righe stimato sarà comunque più preciso (a meno che non si esegua un'operazione su tutte le righe della tabella).SQL Server non gestisce affatto le statistiche delle colonne per le variabili di tabella, quindi ricadrà sulle ipotesi basate sul predicato di confronto (ad esempio, il 10% della tabella verrà restituito per un=contro una colonna non univoca o il 30% per a>confronto).Statistiche della colonna in contrastosiamomantenuto per#temptabelle.

SQL Server mantiene un conteggio del numero di modifiche apportate a ciascuna colonna.Se il numero di modifiche da quando il piano è stato compilato supera la soglia di ricompilazione (RT), il piano verrà ricompilato e le statistiche aggiornate.La RT dipende dal tipo e dalla dimensione della tabella.

A partire dalPlan Caching in SQL Server 2008

RT è calcolato come segue.(n si riferisce alla cardinalità di una tabella quando viene compilato un piano di query.)

Tavolo permanente
- Se n <= 500, RT = 500.
- Se n> 500, RT = 500 + 0.20 * n.

Tavolo temporaneo
- Se n <6, RT = 6.
- Se 6 <= n <= 500, RT = 500.
- Se n> 500, RT = 500 + 0.20 * n.
Tabella variabile
- RT non esiste.Pertanto, le ricompilazioni non si verificano a causa delle modifiche nelle cardinalità delle variabili di tabella.(Ma vedi nota su TF 2453 di seguito)

ilKEEP PLANsuggerimento può essere usato per impostare il RT per#temple tabelle sono le stesse delle tabelle permanenti.

L'effetto netto di tutto questo è che spesso vengono generati i piani di esecuzione#temple tabelle sono ordini di grandezza migliori di quelle@table_variablesquando sono coinvolte molte righe poiché SQL Server ha informazioni migliori con cui lavorare.

NB1: le variabili di tabella non hanno statistiche ma possono ancora generare un evento di ricompilazione "Statistics Changed" sotto il flag di traccia 2453 (non si applica ai piani "banali") Questo sembra verificarsi nelle stesse soglie di ricompilazione mostrate per le tabelle temporanee sopra con un uno aggiuntivo che seN=0 -> RT = 1.cioè tutte le istruzioni compilate quando la variabile table è vuota finiranno per ricevere una ricompilazione e una correzioneTableCardinalityla prima volta vengono eseguiti quando non sono vuoti.La cardinalità della tabella degli orari di compilazione è memorizzata nel piano e se l'istruzione viene eseguita nuovamente con la stessa cardinalità (dovuta al flusso delle istruzioni di controllo o al riutilizzo di un piano memorizzato nella cache) non avviene alcuna ricompilazione.

NB2: per le tabelle temporanee memorizzate nella cache in stored procedure, la storia della ricompilazione è molto più complicata di quanto descritto sopra.VedereTemporary Tables in Stored Proceduresper tutti i dettagli cruenti.

ricompilazioni

Così come le ricompense basate sulla modifica descritte sopra#temple tabelle possono anche essere associate conadditional compilessemplicemente perché consentono operazioni vietate per variabili di tabella che attivano una compilazione (ad esempio, modifiche DDLCREATE INDEX,ALTER TABLE)

Blocco

essohas been statedquelle variabili di tabella non partecipano al blocco.Questo non è il caso.Eseguendo i risultati sottostanti alla scheda Messaggi SSMS, i dettagli dei blocchi presi e rilasciati per un'istruzione di inserimento.

DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

Per domande cheSELECTdalle variabili della tabella Paul White sottolinea nei commenti che questi vengono automaticamente con un implicitoNOLOCKsuggerimento.Questo è mostrato sotto

DECLARE @T TABLE(X INT); 

SELECT X
FROM @T 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

Produzione

*** Output Tree: (trivial plan) ***

       PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )

L'impatto di questo sul blocco potrebbe essere piuttosto secondario.

SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))


DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Nessuno di questi risultati restituisce l'ordine delle chiavi dell'indice che indica che SQL Server ha utilizzato unallocation ordered scanper entrambi.

Ho eseguito lo script sopra due volte e i risultati per la seconda esecuzione sono riportati di seguito

Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0 

L'output di blocco per la variabile di tabella è infatti estremamente minimo poiché SQL Server acquisisce semplicemente un blocco di stabilità dello schema sull'oggetto.Ma per a#temptavolo è quasi leggero in quanto elimina un livello dell'oggettoSserratura.UNNOLOCKsuggerimento oREAD UNCOMMITTEDil livello di isolamento può naturalmente essere specificato esplicitamente quando si lavora con#temptavoli pure.

Analogamente al problema con la registrazione di una transazione utente circostante può significare che i blocchi sono trattenuti per più tempo#temptabelle.Con lo script qui sotto

  --BEGIN TRAN;  

     CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

     INSERT INTO #T (X) VALUES(1) 

     SELECT CASE resource_type
          WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
          WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                         FROM tempdb.sys.allocation_units a 
                         JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                         WHERE a.allocation_unit_id = resource_associated_entity_id)
          WHEN 'DATABASE' THEN DB_NAME(resource_database_id)                   
          ELSE (SELECT OBJECT_NAME(object_id, 2)
             FROM  tempdb.sys.partitions
             WHERE partition_id = resource_associated_entity_id)
         END AS object_name,
         *
     FROM  sys.dm_tran_locks
     WHERE request_session_id = @@SPID

     DROP TABLE #T

     -- ROLLBACK 

quando viene eseguito al di fuori di una transazione utente esplicita per entrambi i casi, l'unico blocco restituito durante il controllosys.dm_tran_locksè un blocco condiviso suDATABASE.

Su decommentando ilBEGIN TRAN ... ROLLBACKVengono restituite 26 righe che mostrano che i blocchi sono trattenuti sia sull'oggetto stesso che sulle righe della tabella di sistema per consentire il rollback e impedire ad altre transazioni di leggere i dati non salvati.L'operazione della variabile di tabella equivalente non è soggetta al rollback con la transazione dell'utente e non è necessario mantenere questi blocchi per consentirci di controllare l'istruzione successiva, ma i lucchetti di tracciamento acquisiti e rilasciati in Profiler o utilizzando il flag di traccia 1200 mostrano molti eventi di blocco ancora si verificano.

indici

Per le versioni precedenti agli indici di SQL Server 2014 è possibile creare solo implicitamente sulle variabili di tabella come effetto collaterale dell'aggiunta di un vincolo univoco o di una chiave primaria.Questo ovviamente significa che sono supportati solo indici unici.Un indice non univoco non cluster su una tabella con un indice cluster univoco può essere simulato tuttavia semplicemente dichiarandoloUNIQUE NONCLUSTEREDe aggiungendo la chiave CI alla fine della chiave NCI desiderata (farebbe SQL Server)do this behind the scenes anywayanche se potrebbe essere specificato un NCI non unico)

Come dimostrato in precedenza variindex_options può essere specificato nella dichiarazione di vincolo inclusoDATA_COMPRESSION,IGNORE_DUP_KEY, eFILLFACTOR(anche se non ha senso impostarlo perché non farebbe alcuna differenza nella ricostruzione dell'indice e non è possibile ricostruire gli indici sulle variabili di tabella!)

Inoltre, le variabili di tabella non supportanoINCLUDEd colonne, indici filtrati (fino al 2016) o partizionamento,#temptabelle (lo schema di partizione deve essere creato intempdb).

Indici in SQL Server 2014

Gli indici non univoci possono essere dichiarati in linea nella definizione della variabile di tabella in SQL Server 2014. Di seguito è riportata la sintassi di esempio.

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Indici in SQL Server 2016

Da CTP 3.1 è ora possibile dichiarare gli indici filtrati per le variabili di tabella.Da RTM itpotrebbeSia il caso che siano incluse anche le colonne anche se lorowill likely not make it into SQL16 due to resource constraints

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Parallelismo

Query che inseriscono (o altrimenti modificano)@table_variablesnon può avere un piano parallelo,#temp_tablesnon sono limitati in questo modo.

C'è una soluzione apparente in tale riscrittura come segue consenteSELECTparte deve svolgersi in parallelo ma finisce per utilizzare una tabella temporanea nascosta(behind the scenes)

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

Non esiste una tale limitazione nelle queryselezionareda variabili di tabellaas illustrated in my answer here

Altre differenze funzionali

  • #temp_tablesnon può essere utilizzato all'interno di una funzione.@table_variablespuò essere utilizzato all'interno di UDF della tabella scalare o multipla.
  • @table_variablesnon può avere vincoli denominati.
  • @table_variablesnon può essereSELECT-edINTO,ALTER-ed,TRUNCATEd o essere il bersaglio diDBCCcomandi comeDBCC CHECKIDENTo diSET IDENTITY INSERTe non supportano suggerimenti sulla tabella comeWITH (FORCESCAN)
  • CHECKi vincoli sulle variabili di tabella non vengono considerati dall'ottimizzatore per la semplificazione, i predicati impliciti o il rilevamento di contraddizioni.
  • Le variabili di tabella non sembrano qualificarsi per ilrowset sharing optimisationil che significa che eliminare e aggiornare i piani contro questi può incontrare ulteriori sovraccarichi ePAGELATCH_EXattende.(Example)

Solo memoria?

Come affermato all'inizio, entrambi vengono memorizzati nelle pagine intempdb.Tuttavia, non ho considerato se ci fossero differenze di comportamento quando si tratta di scrivere queste pagine su disco.

Ho fatto una piccola quantità di test su questo ora e finora non ho visto alcuna differenza.Nel test specifico che ho fatto sulla mia istanza di SQL Server 250 pagine sembra essere il punto di interruzione prima che il file di dati venga scritto.

NB: il seguente comportamento non si verifica più in SQL Server 2014 oSQL Server 2012 SP1/CU10 or SP2/CU1lo scrittore entusiasta non è più così desideroso di scrivere pagine su disco.Maggiori dettagli su questo cambiamento inSQL Server 2014: tempdb Hidden Performance Gem.

Esecuzione dello script sottostante

CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

E il monitoraggio scrive sutempdbfile di dati con Process Monitor non ne ho visto nessuno (tranne occasionalmente quelli nella pagina di avvio del database con offset 73.728).Dopo aver cambiato250a251Ho iniziato a vedere le scritture come di seguito.

ProcMon

Lo screenshot in alto mostra scritture 5 * 32 pagine e una sola pagina di scrittura che indica che 161 pagine sono state scritte su disco.Ho ottenuto lo stesso punto di interruzione di 250 pagine quando eseguo il test anche con variabili di tabella.Lo script qui sotto mostra un modo diverso di guardaresys.dm_os_buffer_descriptors

DECLARE @T TABLE (
    X    INT,
    [dba.se] CHAR(8000) NULL)

INSERT INTO @T
         (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM  master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                    FROM  tempdb.sys.partitions AS p
                   INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                        ON p.hobt_id = a.container_id
                       INNER JOIN tempdb.sys.columns AS c
                        ON c.object_id = p.object_id
                    WHERE c.name = 'dba.se')
GROUP BY is_modified 

risultati

is_modified page_count
----------- -----------
0      192
1      61

Mostrando che 192 pagine sono state scritte su disco e la bandiera sporca è stata cancellata.Mostra anche che essere scritti su disco non significa che le pagine verranno espulse dal pool di buffer immediatamente.Le query su questa variabile di tabella potrebbero ancora essere soddisfatte interamente dalla memoria.

Su un server inattivo conmax server memoryimpostato2000 MBeDBCC MEMORYSTATUSsegnalazione di pagine pool di buffer allocate come circa 1.843.000 KB (circa 23.000 pagine) Ho inserito le tabelle sopra in gruppi di 1.000 righe/pagine e per ogni iterazione registrata.

SELECT Count(*)
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE' 

Sia la variabile tabella e il#templa tabella forniva grafici quasi identici e riusciva a ridurre al massimo il pool di buffer prima di arrivare al punto che non erano interamente tenuti in memoria, quindi non sembra esserci alcuna limitazione particolare sulla quantità di memoria che può consumare.

Pages in Buffer Pool


35

Ci sono alcune cose che vorrei sottolineare in base a esperienze particolari piuttosto che allo studio.Come DBA, sono molto nuovo quindi per favore correggimi dove richiesto.

  1. Le tabelle #temp utilizzano per impostazione predefinita le regole di confronto predefinite dell'istanza di SQL Server.Pertanto, se non diversamente specificato, è possibile che si verifichino problemi nel confronto o nell'aggiornamento dei valori tra le tabelle #temp e le tabelle del database, se il masterdb presenta una raccolta diversa dal database.Vedi: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Completamente basato sull'esperienza personale, la memoria disponibile sembra avere un effetto sul quale funziona meglio.MSDN consiglia l'uso di variabili di tabella per archiviare set di risultati più piccoli, ma la maggior parte delle volte la differenza non è nemmeno evidente.Negli insiemi più grandi, tuttavia, in alcuni casi è evidente che le variabili di tabella richiedono molta più memoria e possono rallentare la ricerca fino alla ricerca per indicizzazione.
+5

Si noti inoltre che le regole di confronto sulle tabelle #temp * possono * ereditare le regole di confronto del database chiamante se si utilizza SQL Server 2012 e il database è contenuto. 29 gen. 132013-01-29 19:51:03

  0

Chiarimento al n. 2 per insiemi di piccole dimensioni e grandi http://stackoverflow.com/a/14465163/5224021 08 feb. 172017-02-08 14:56:07