Was ist der Unterschied zwischen einer temporären Tabelle und einer Tabellenvariablen in SQL Server?


404

Dies scheint ein Gebiet mit einigen Mythen und widersprüchlichen Ansichten zu sein.

Was ist der Unterschied zwischen einer Tabellenvariablen und einer lokalen temporären Tabelle in SQL Server?

+4

Verwandte Frage zum Stapelüberlauf: [Wann sollte ich in SQL Server eine Tabellenvariable oder eine temporäre Tabelle verwenden?] (Http://stackoverflow.com/q/11857789/73226) 07 jul. 162016-07-07 04:30:51

618

Inhalt

Contents

Vorbehalt

In dieser Antwort werden die in SQL Server 2000 eingeführten "klassischen" Tabellenvariablen erläutert. SQL Server 2014 im Speicher OLTP führt speicheroptimierte Tabellentypen ein.Tabellenvariableninstanzen davon unterscheiden sich in vielerlei Hinsicht von den unten diskutierten!(more details).

Lagerraum

Kein Unterschied.Beide sind in gespeicherttempdb.

Ich habe gesehen, dass dies für Tabellenvariablen nicht immer der Fall ist, dies kann jedoch anhand der folgenden Tabelle überprüft werden

DECLARE @T TABLE(X INT)

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

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

Beispielergebnisse (Anzeige des Standorts intempdbdie 2 Zeilen werden gespeichert)

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

Logischer Ort

@table_variablesverhalten sich eher so, als wären sie Teil der aktuellen Datenbank als#tempTabellen tun.Für Tabellenvariablen (seit 2005) werden Spaltenkollatierungen, wenn nicht explizit angegeben, die der aktuellen Datenbank sein, während für#tempTabellen verwendet die Standardkollatierung vontempdb(More details).Außerdem müssen benutzerdefinierte Datentypen und XML-Auflistungen in tempdb vorliegen, um verwendet werden zu können#tempTabellen, aber Tabellenvariablen können sie aus der aktuellen Datenbank verwenden (Source).

SQL Server 2012 führt enthaltene Datenbanken ein.the behavior of temporary tables in these differs(h/t Aaron)

In einer enthaltenen Datenbank werden temporäre Tabellendaten in der Kollation der enthaltenen Datenbank gesammelt.

  • Alle mit temporären Tabellen verknüpften Metadaten (z. B. Tabellen- und Spaltennamen, Indizes usw.) befinden sich in der Katalogsortierung.
  • Benannte Einschränkungen dürfen in temporären Tabellen nicht verwendet werden.
  • Temporäre Tabellen verweisen möglicherweise nicht auf benutzerdefinierte Typen, XML-Schemasammlungen oder benutzerdefinierte Funktionen.

Sichtbarkeit für verschiedene Bereiche

@table_variablesDer Zugriff ist nur innerhalb des Stapels und Bereichs möglich, in dem sie deklariert sind.#temp_tablesinnerhalb von untergeordneten Stapeln zugänglich sind (verschachtelte Trigger, Prozedur,execAnrufe).#temp_tablesam äußeren Umfang erstellt (@@NESTLEVEL=0) können sich auch über mehrere Stapel erstrecken, da sie bis zum Ende der Sitzung bestehen bleiben.Keiner der Objekttypen kann in einem untergeordneten Stapel erstellt und im aufrufenden Bereich aufgerufen werden, wie im Folgenden beschrieben (global)##tempTabellenkönnenaber sein).

Lebenszeit

@table_variableswerden implizit erstellt, wenn ein Batch mit aDECLARE @.. TABLEDie Anweisung wird ausgeführt (bevor ein Benutzercode in diesem Stapel ausgeführt wird) und am Ende implizit gelöscht.

Obwohl der Parser nicht zulässt, dass Sie versuchen, die Tabellenvariable vor dem zu verwendenDECLAREAussage die implizite Erstellung ist unten zu sehen.

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

--Works fine
SELECT *
FROM @T

#temp_tableswerden explizit beim TSQL angelegtCREATE TABLEAnweisung ist angetroffen und kann explizit mit gelöscht werdenDROP TABLEoder wird implizit gelöscht, wenn der Stapel endet (falls in einem untergeordneten Stapel mit erstellt)@@NESTLEVEL > 0) oder wenn die Sitzung anderweitig endet.

NB: In gespeicherten Routinen beide Objekttypencan be cachedanstatt wiederholt neue Tabellen zu erstellen und zu löschen.Es gibt jedoch Einschränkungen, wann dieses Caching auftreten kann, die möglicherweise verletzt werden#temp_tableswas aber die einschränkungen betrifft@table_variablessowieso verhindern.Der Wartungsaufwand für den Cache#tempTabellen istleichtgrößer als für Tabellenvariablenas illustrated here.

Objekt-Metadaten

Dies ist im Wesentlichen für beide Objekttypen gleich.Es ist in den System-Basistabellen in gespeicherttempdb.Es ist einfacher zu sehen, für eine#tempTisch jedoch alsOBJECT_ID('tempdb..#T')kann verwendet werden, um in die Systemtabellen einzugeben, und der intern generierte Name wird enger mit dem in der Tabelle definierten Namen korreliertCREATE TABLEAussage.Für Tabellenvariablen dieobject_idFunktion funktioniert nicht und der interne Name wird vollständig vom System generiert, ohne dass eine Beziehung zum Variablennamen besteht.Das Folgende zeigt, dass die Metadaten immer noch vorhanden sind, indem Sie einen (hoffentlich eindeutigen) Spaltennamen eingeben.Bei Tabellen ohne eindeutigen Spaltennamen kann die object_id mit ermittelt werdenDBCC PAGEsolange sie nicht leer sind.

/*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'

Ausgabe

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         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

Transaktionen

Operationen auf@table_variableswerden als Systemtransaktionen ausgeführt, unabhängig von jeglichen externen Benutzertransaktionen, während die entsprechenden Transaktionen ausgeführt werden#tempTabellenoperationen würden als Teil der Benutzertransaktion selbst ausgeführt.Aus diesem Grund aROLLBACKBefehl wirkt sich auf a aus#tempTisch, aber lassen Sie die@table_variableunberührt.

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

Protokollierung

Beide erzeugen Protokollsätze zumtempdbTransaktionsprotokoll.Ein häufiges Missverständnis ist, dass dies bei Tabellenvariablen nicht der Fall ist. Ein Skript, das dies demonstriert, deklariert eine Tabellenvariable, fügt ein paar Zeilen hinzu, aktualisiert sie dann und löscht sie.

Da die Tabellenvariable zu Beginn und am Ende des Stapels implizit erstellt und gelöscht wird, müssen mehrere Stapel verwendet werden, um die vollständige Protokollierung anzuzeigen.

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 

Kehrt zurück

Detaillierte Ansicht

Screenshot of results

Zusammenfassungsansicht (einschließlich Protokollierung für implizite Drop- und Systembasistabellen)

Screenshot of results

As far as I've been able to discernOperationen auf beiden erzeugen ungefähr die gleiche Menge an Protokollierung.

Während derMengeder Protokollierung ist sehr ähnlich, ein wichtiger Unterschied ist, dass Protokollsätze im Zusammenhang mit#tempTabellen können erst gelöscht werden, wenn eine Benutzertransaktion abgeschlossen ist, in die zu einem bestimmten Zeitpunkt eine lang laufende Transaktion geschrieben wird#tempTabellen verhindern das Abschneiden von ProtokollentempdbDie autonomen Transaktionen, die für Tabellenvariablen erzeugt wurden, tun dies nicht.

Tabellenvariablen werden nicht unterstütztTRUNCATEDies kann zu einem Protokollierungsnachteil führen, wenn alle Zeilen aus einer Tabelle entfernt werden müssen (dies gilt jedoch für sehr kleine TabellenDELETEcan work out better anyway)

Kardinalität

Viele der Ausführungspläne mit Tabellenvariablen enthalten eine einzelne Zeile, die als Ausgabe von ihnen geschätzt wird.Die Überprüfung der Eigenschaften der Tabellenvariablen zeigt, dass SQL Server der Ansicht ist, dass die Tabellenvariable über Folgendes verfügtNullZeilen (Warum es schätzt, dass 1 Zeile aus einer Nullzeilentabelle ausgegeben wird, wird von @Paul White erklärthere).

Die im vorherigen Abschnitt gezeigten Ergebnisse zeigen jedoch eine Genauigkeitrowsmitzählensys.partitions.Das Problem ist, dass in den meisten Fällen die Anweisungen, die auf Tabellenvariablen verweisen, kompiliert werden, während die Tabelle leer ist.Wenn die Anweisung nach (neu) kompiliert wird@table_variablewird aufgefüllt, dann wird dies stattdessen für die Kardinalität der Tabelle verwendet (dies kann aufgrund eines expliziten Ereignisses geschehen)recompileoder möglicherweise, weil die Anweisung auch auf ein anderes Objekt verweist, das eine verzögerte Kompilierung oder eine Neukompilierung verursacht.)

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

Der Plan zeigt die genaue geschätzte Zeilenanzahl nach der verzögerten Kompilierung.

Shows accurate row count

In SQL Server 2012 SP2 wird das Ablaufverfolgungsflag 2453 eingeführt.Weitere Details finden Sie unter "Relational Engine"here.

Wenn dieses Ablaufverfolgungsflag aktiviert ist, kann es dazu führen, dass bei der automatischen Neukompilierung die geänderte Kardinalität berücksichtigt wird, wie in Kürze erläutert wird.

Keine Spaltenstatistik

Eine genauere Tabellenkardinalität bedeutet jedoch nicht, dass die geschätzte Zeilenzahl genauer ist (es sei denn, Sie führen eine Operation für alle Zeilen in der Tabelle aus).SQL Server verwaltet überhaupt keine Spaltenstatistiken für Tabellenvariablen und greift daher auf Vermutungen zurück, die auf dem Vergleichsprädikat basieren (z. B. dass 10% der Tabelle für eine zurückgegeben werden=gegen eine nicht eindeutige Spalte oder 30% für a>Vergleich).Im Gegensatz dazu Spaltenstatistiksindgepflegt für#tempTabellen.

SQL Server zählt die Anzahl der Änderungen, die an jeder Spalte vorgenommen wurden.Wenn die Anzahl der Änderungen seit dem Kompilieren des Plans den Neukompilierungsschwellenwert (RT) überschreitet, wird der Plan neu kompiliert und die Statistik aktualisiert.Die RT hängt vom Tabellentyp und der Größe ab.

VonPlan Caching in SQL Server 2008

RT wird wie folgt berechnet.(n bezieht sich auf die Kardinalität einer Tabelle, wenn ein Abfrageplan kompiliert wird.)

Ständiger Tisch
- Wenn n <= 500, RT = 500.
- Wenn n> 500, ist RT = 500 + 0,20 * n.

Temporärer Tisch
- Wenn n <6, ist RT = 6.
- Wenn 6 <= n <= 500, RT = 500.
- Wenn n> 500, ist RT = 500 + 0,20 * n.
Tabellenvariable
- RT existiert nicht.Daher finden Neukompilierungen aufgrund von Änderungen der Kardinalitäten von Tabellenvariablen nicht statt.(Siehe Hinweis zu TF 2453 weiter unten)

dasKEEP PLANMit hint kann die RT für eingestellt werden#tempTabellen wie bei permanenten Tabellen.

Der Nettoeffekt davon ist, dass oft die Ausführungspläne für generiert#tempTabellen sind um Größenordnungen besser als für@table_variablesWenn viele Zeilen betroffen sind, hat SQL Server bessere Informationen zum Arbeiten.

NB1: Tabellenvariablen haben keine Statistik, können jedoch ein Neukompilierungsereignis "Statistik geändert" unter Ablaufverfolgungsflag 2453 verursachen (gilt nicht für "triviale" Pläne). Dies scheint unter denselben Neukompilierungsschwellen aufzutreten, wie oben für temporäre Tabellen mit einem angegeben zusätzliche, wennN=0 -> RT = 1.dh alle Anweisungen, die kompiliert werden, wenn die Tabellenvariable leer ist, werden neu kompiliert und korrigiertTableCardinalityBeim ersten Mal werden sie ausgeführt, wenn sie nicht leer sind.Die Kardinalität des Kompilierungszeitplans wird im Plan gespeichert, und wenn die Anweisung erneut mit derselben Kardinalität ausgeführt wird (entweder aufgrund von Steueranweisungen oder der Wiederverwendung eines zwischengespeicherten Plans), erfolgt keine Neukompilierung.

Anmerkung 2: Für zwischengespeicherte temporäre Tabellen in gespeicherten Prozeduren ist die Rekompilierungsgeschichte viel komplizierter als oben beschrieben.SehenTemporary Tables in Stored Proceduresfür alle blutigen Details.

Kompiliert neu

Sowie die oben beschriebenen modifikationsbasierten Neukompilierungen#tempTabellen können auch zugeordnet werdenadditional compileseinfach, weil sie Operationen zulassen, die für Tabellenvariablen verboten sind, die eine Kompilierung auslösen (z. B. DDL-Änderungen)CREATE INDEX,ALTER TABLE)

Verriegelung

Eshas been statedDiese Tabellenvariablen nehmen nicht am Sperren teil.Das ist nicht der Fall.Durch Ausführen der folgenden Ausgaben auf der Registerkarte SSMS-Nachrichten werden die Details der Sperren angezeigt, die für eine insert-Anweisung verwendet und freigegeben wurden.

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)

Für Fragen, dieSELECTPaul White weist aus den Tabellenvariablen in den Kommentaren darauf hin, dass diese automatisch mit einem Implizit einhergehenNOLOCKHinweis.Dies ist unten dargestellt

DECLARE @T TABLE(X INT); 

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

Ausgabe

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

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

Die Auswirkung auf das Sperren ist jedoch möglicherweise recht gering.

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

Keine dieser Rückgaben führt zu einer Indexschlüsselreihenfolge, die darauf hinweist, dass SQL Server eine verwendet hatallocation ordered scanfür beide.

Ich habe das obige Skript zweimal ausgeführt und die Ergebnisse für den zweiten Durchlauf sind unten

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 

Die Sperrausgabe für die Tabellenvariable ist in der Tat äußerst gering, da SQL Server nur eine Schemastabilitätssperre für das Objekt erhält.Aber für eine#tempTisch ist es fast so leicht, dass es eine Objektebene herausnimmtSsperren.EINNOLOCKHinweis oderREAD UNCOMMITTEDDie Isolationsstufe kann natürlich bei der Arbeit mit explizit angegeben werden#tempTische auch.

Ähnlich wie bei der Protokollierung kann eine umgebende Benutzertransaktion dazu führen, dass die Sperren länger aufrechterhalten werden#tempTabellen.Mit dem Skript unten

  --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 

Bei Ausführung außerhalb einer expliziten Benutzertransaktion wird in beiden Fällen die einzige Sperre bei der Prüfung zurückgegebensys.dm_tran_locksist eine gemeinsame Sperre für dieDATABASE.

Beim Auskommentieren derBEGIN TRAN ... ROLLBACKEs werden 26 Zeilen zurückgegeben. Dies zeigt, dass Sperren sowohl für das Objekt selbst als auch für Systemtabellenzeilen gelten, um ein Rollback zu ermöglichen und zu verhindern, dass andere Transaktionen nicht festgeschriebene Daten lesen.Die entsprechende Tabellenvariablenoperation unterliegt bei der Benutzertransaktion keinem Rollback und muss diese Sperren nicht halten, damit wir die nächste Anweisung einchecken können. Die Ablaufverfolgung von Sperren, die im Profiler erworben und freigegeben wurden oder die das Ablaufverfolgungsflag 1200 verwenden, zeigt jedoch, dass noch zahlreiche Sperrereignisse vorhanden sind auftreten.

Indizes

In Versionen vor SQL Server 2014 können Indizes nur implizit für Tabellenvariablen als Nebeneffekt des Hinzufügens einer eindeutigen Einschränkung oder eines eindeutigen Primärschlüssels erstellt werden.Dies bedeutet natürlich, dass nur eindeutige Indizes unterstützt werden.Ein nicht eindeutiger nicht gruppierter Index für eine Tabelle mit einem eindeutigen gruppierten Index kann jedoch simuliert werden, indem er einfach deklariert wirdUNIQUE NONCLUSTEREDund Hinzufügen des CI-Schlüssels am Ende des gewünschten NCI-Schlüssels (SQL Server würdedo this behind the scenes anywayauch wenn ein nicht eindeutiger NCI angegeben werden könnte)

Wie bereits gezeigt, verschiedeneindex_options kann in der Constraint-Deklaration einschließlich angegeben werdenDATA_COMPRESSION,IGNORE_DUP_KEY, undFILLFACTOR(Es macht jedoch keinen Sinn, dies festzulegen, da dies nur einen Unterschied bei der Neuerstellung des Index bedeuten würde und Sie keine Indizes für Tabellenvariablen neu erstellen können!)

Außerdem werden Tabellenvariablen nicht unterstütztINCLUDEd Spalten, gefilterte Indizes (bis 2016) oder Partitionierung,#tempTabellen tun (das Partitionsschema muss in erstellt werdentempdb).

Indizes in SQL Server 2014

Nicht eindeutige Indizes können in der Tabellenvariablendefinition in SQL Server 2014 inline deklariert werden. Die Beispielsyntax hierfür ist unten aufgeführt.

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*/
);

Indizes in SQL Server 2016

Ab CTP 3.1 ist es jetzt möglich, gefilterte Indizes für Tabellenvariablen zu deklarieren.Mit RTM ist eskannDies kann der Fall sein, wenn auch eingeschlossene Spalten zulässig sindwill 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*/
)

Parallelität

Abfragen, die eingefügt (oder anderweitig geändert) werden@table_variableskann keinen parallelen Plan haben,#temp_tablessind auf diese Weise nicht eingeschränkt.

Es gibt eine offensichtliche Problemumgehung, die das Umschreiben wie folgt ermöglichtSELECTTeil parallel stattfinden, aber das endet mit einer versteckten temporären Tabelle(behind the scenes)

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

Es gibt keine solche Einschränkung bei Abfragen, diewählenaus Tabellenvariablenas illustrated in my answer here

Andere funktionale Unterschiede

  • #temp_tablesKann nicht in einer Funktion verwendet werden.@table_variablesKann in skalaren oder Tabellen-UDFs mit mehreren Anweisungen verwendet werden.
  • @table_variableskann keine benannten Bedingungen haben.
  • @table_variableskann nicht seinSELECT-edINTO,ALTER-ed,TRUNCATEd oder das Ziel vonDBCCBefehle wieDBCC CHECKIDENTOder vonSET IDENTITY INSERTund unterstütze keine Tabellenhinweise wieWITH (FORCESCAN)
  • CHECKEinschränkungen für Tabellenvariablen werden vom Optimierer zur Vereinfachung, für implizite Prädikate oder zur Erkennung von Widersprüchen nicht berücksichtigt.
  • Tabellenvariablen scheinen sich nicht für die zu qualifizierenrowset sharing optimisationDas bedeutet, dass das Löschen und Aktualisieren von Plänen für diese mehr Aufwand verursachen kannPAGELATCH_EXwartet.(Example)

Nur Speicher?

Wie eingangs erwähnt, werden beide auf Seiten in gespeicherttempdb.Ich habe jedoch nicht angesprochen, ob es einen Unterschied im Verhalten beim Schreiben dieser Seiten auf Disc gibt.

Ich habe dies jetzt ein wenig getestet und bisher keinen solchen Unterschied festgestellt.In dem spezifischen Test, den ich auf meiner Instanz von SQL Server 250 durchgeführt habe, scheint es, dass Seiten der Cut-Off-Punkt sind, bevor die Datendatei geschrieben wird.

Hinweis: Das folgende Verhalten tritt in SQL Server 2014 oder nicht mehr aufSQL Server 2012 SP1/CU10 or SP2/CU1Der eifrige Schreiber ist nicht mehr so ​​eifrig, Seiten auf eine Disc zu schreiben.Weitere Details zu dieser Änderung unterSQL Server 2014: tempdb Hidden Performance Gem.

Führen Sie das folgende Skript aus

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

Und Monitoring schreibt an dietempdbDatendatei mit Prozessmonitor Ich habe keine gesehen (außer gelegentlich auf der Datenbank-Boot-Seite bei Versatz 73.728).Nach dem Wechsel250zu251Ich fing an, die folgenden Schriften zu sehen.

ProcMon

Der obige Screenshot zeigt 5 * 32 Seitenschreibvorgänge und einen einzelnen Seitenschreibvorgang, der angibt, dass 161 der Seiten auf eine Disc geschrieben wurden.Ich habe den gleichen Grenzwert von 250 Seiten beim Testen mit Tabellenvariablen erhalten.Das folgende Skript zeigt es auf eine andere Art und Weisesys.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 

Ergebnisse

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

Es wurde angezeigt, dass 192 Seiten auf die Disc geschrieben und die unsaubere Flagge gelöscht wurden.Es zeigt auch, dass das Beschreiben von Datenträgern nicht bedeutet, dass Seiten sofort aus dem Pufferpool entfernt werden.Die Abfragen für diese Tabellenvariable konnten immer noch vollständig aus dem Speicher ausgeführt werden.

Auf einem inaktiven Server mitmax server memoryeinstellen2000 MBundDBCC MEMORYSTATUSBerichterstellung für Pufferpoolseiten Zugeteilt als ca. 1.843.000 KB (ca. 23.000 Seiten) Ich habe in Stapeln von 1.000 Zeilen/Seiten und für jede aufgezeichnete Iteration in die obigen Tabellen eingefügt.

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

Sowohl die Tabellenvariable als auch die#temptable gab nahezu identische Diagramme aus und schaffte es, den Pufferpool so gut wie vollständig auszulasten, bevor man zu dem Punkt kam, dass sie nicht vollständig im Arbeitsspeicher gespeichert waren. Es scheint also keine besondere Einschränkung hinsichtlich des verfügbaren Arbeitsspeichers zu geben.

Pages in Buffer Pool


35

Es gibt ein paar Dinge, auf die ich eher aufgrund bestimmter Erfahrungen hinweisen möchte, als dass ich sie studiere.Als DBA bin ich sehr neu. Bitte korrigieren Sie mich, wo erforderlich.

  1. # temporäre Tabellen verwenden standardmäßig die Standardkollatierung der SQL Server-Instanz.Sofern nicht anders angegeben, können Probleme beim Vergleichen oder Aktualisieren von Werten zwischen # temporären Tabellen und Datenbanktabellen auftreten, wenn die Masterdb eine andere Sortierung als die Datenbank aufweist.Siehe: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Ganz auf der Grundlage persönlicher Erfahrungen scheint sich der verfügbare Speicher positiv auf die Leistung auszuwirken.MSDN empfiehlt die Verwendung von Tabellenvariablen zum Speichern kleinerer Ergebnismengen, aber meistens ist der Unterschied nicht einmal spürbar.In größeren Mengen wird jedoch in einigen Fällen deutlich, dass Tabellenvariablen viel speicherintensiver sind und die Abfrage bis zum Durchforsten verlangsamen können.
+5

Beachten Sie auch, dass die Kollatierung in # temporären Tabellen die Kollatierung der aufrufenden Datenbank erben kann, wenn Sie SQL Server 2012 verwenden und die Datenbank enthalten ist. 29 jan. 132013-01-29 19:51:03

  0

Erläuterung zu Nummer 2 für kleine und große Sets http://stackoverflow.com/a/14465163/5224021 08 feb. 172017-02-08 14:56:07