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

Question

In SQL Server 2005 können wir temporäre Tabellen auf zwei Arten erstellen:

declare @tmp table (Col1 int, Col2 int);

o

create table #tmp (Col1 int, Col2 int);

Was sind die Unterschiede zwischen diesen beiden? Ich habe widersprüchliche Meinungen darüber gelesen, ob @tmp noch tempdb verwendet, oder ob alles im Speicher passiert.

In welchen Szenarien ist das eine besser als das andere?

Answer 1

Zitat entnommen aus; Professionelle SQL Server 2012 Interna und Fehlerbehebung

Statistik Der Hauptunterschied zwischen temporären Tabellen und Tabellenvariablen ist, dass keine Statistiken über Tabellenvariablen erstellt werden. Dies hat zwei wesentliche Konsequenzen, von denen die erste darin besteht, dass der Query Optimizer eine eine feste Schätzung für die Anzahl der Zeilen in einer Tabellenvariablen unabhängig davon, welche Daten sie enthält. Außerdem ändert das Hinzufügen oder Entfernen Daten die Schätzung nicht ändern.

Indizes Sie können keine Indizes auf Tabellenvariablen erstellen, aber Sie können Beschränkungen erstellen. Das bedeutet, dass Sie durch das Erstellen von Primärschlüsseln oder eindeutigen Constraints Indizes auf Tabellenvariablen haben können (da diese zur Unterstützung von Constraints) auf Tabellenvariablen haben. Selbst wenn Sie Beschränkungen haben, und daher Indizes mit Statistiken haben, werden die Indizes nicht werden die Indizes nicht verwendet, wenn die Abfrage kompiliert wird, da sie zur Kompilier Kompilierungszeit nicht existieren und auch keine Neukompilierungen verursachen.

Schema-Änderungen Schema-Änderungen sind möglich bei temporären Tabellen möglich, nicht aber an Tabellenvariablen. Obwohl Schemamodifikationen auf temporären Tabellen möglich sind, sollten Sie diese vermeiden, da sie zu Neukompilierung von Anweisungen, die die Tabellen verwenden, verursachen.

TABELLENVARIABLEN WERDEN NICHT IM SPEICHER ANGELEGT

Es ist ein weit verbreiteter Irrglaube, dass Tabellenvariablen speicherinterne Strukturen sind. sind und als solche schneller arbeiten als temporäre Tabellen . Dank eines DMV namens sys . dm _ db _ session _ space _ usage , der die tempdb-Nutzung nach Sitzung, Sie können beweisen, dass das nicht der Fall ist. . Nach dem Neustart von SQL Server zum Löschen der DMV zu löschen, führen Sie das folgende Skript aus, um zu bestätigen, dass Ihre Session _ id 0 für user _ objects _ alloc _ page _ count :

SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Jetzt können Sie überprüfen, wie viel Platz eine temporäre Tabelle verbraucht, indem Sie folgendes ausführen Skript ausführen, um eine temporäre Tabelle mit einer Spalte zu erstellen und sie mit einer Zeile zu füllen:

CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Die Ergebnisse auf meinem Server zeigen, dass der Tabelle eine Seite in tempdb zugewiesen wurde. Führen Sie nun das gleiche Skript aus, aber verwenden Sie eine Tabellenvariable dieses Mal:

DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

Welches ist zu verwenden?

Ob Sie temporäre Tabellen oder Tabellenvariablen verwenden oder nicht, sollte durch gründliche Tests entscheiden, aber ist es am besten, sich für eine vorübergehende Lösung zu entscheiden. Tabellen als Standard, weil es viel weniger Dinge gibt, die schief gehen können falsch .

Ich habe Kunden erlebt, die Code mit Tabellenvariablen entwickelt haben, weil sie mit einer kleinen Anzahl von Zeilen zu tun hatten, und das war schneller als eine temporäre Tabelle, aber ein paar Jahre später gab es Hunderte von tausende von Zeilen in der Tabellenvariable und die Leistung war schrecklich, Versuchen Sie also, bei Ihrer Entscheidung eine gewisse Kapazitätsplanung zu berücksichtigen Entscheidung!

Answer 2

Der andere Hauptunterschied besteht darin, dass Tabellenvariablen keine Spaltenstatistiken haben, während temp-Tabellen diese haben. Das bedeutet, dass der Abfrageoptimierer nicht weiß, wie viele Zeilen sich in der Tabellenvariablen befinden (er schätzt 1), was dazu führen kann, dass höchst unoptimale Pläne erstellt werden, wenn die Tabellenvariable tatsächlich eine große Anzahl von Zeilen enthält.

Answer 3

Ein weiterer Unterschied:

Auf eine Tabelle var kann nur von Anweisungen innerhalb der Prozedur, die sie erzeugt, zugegriffen werden, nicht von anderen Prozeduren, die von dieser Prozedur aufgerufen werden, oder von geschachteltem dynamischen SQL (über exec oder sp_executesql).

Der Geltungsbereich einer temporären Tabelle umfasst dagegen auch Code in aufgerufenen Prozeduren und verschachteltem dynamischem SQL.

Wenn die von Ihrer Prozedur erstellte Tabelle von anderen aufgerufenen Prozeduren oder dynamischem SQL zugänglich sein muss, müssen Sie eine temporäre Tabelle verwenden. Dies kann in komplexen Situationen sehr nützlich sein.

Answer 4

Es überrascht mich, dass niemand den Hauptunterschied zwischen diesen beiden erwähnt hat, nämlich dass die temp-Tabelle die Paralleleinsatz während die Tabellenvariable dies nicht tut. Sie sollten den Unterschied anhand des Ausführungsplans erkennen können. Und hier ist das Video des SQL-Workshops auf Kanal 9 .

Dies erklärt auch, warum Sie eine Tabellenvariable für kleinere Tabellen verwenden sollten, andernfalls verwenden Sie eine temporäre Tabelle, wie SQLMenace antwortete vor.

Answer 5

Unterschiede zwischen Temporary Tables (##temp/#temp) y Table Variables (@table) sind als:

1. Table variable (@table) wird in der memory . in der Erwägung, dass ein Temporary table (##temp/#temp) wird in der tempdb database . Wenn jedoch ein Speicherplatzmangel besteht, können die zu einer Tabellenvariablen gehörenden Seiten in die tempdb verschoben werden.

2. Table variables kann nicht beteiligt sein an transactions, logging or locking . Dies macht @table faster then #temp . Eine variable Tabelle ist also schneller als eine temporäre Tabelle.

3. Temporary table erlaubt Schemaänderungen im Gegensatz zu Table variables .

4. Temporary tables sind in der erstellten Routine und auch in den untergeordneten Routinen sichtbar. Tabellenvariablen hingegen sind nur in der erstellten Routine sichtbar.

5. Temporary tables sind erlaubt CREATE INDEXes in der Erwägung, dass, Table variables sind nicht erlaubt CREATE INDEX stattdessen können sie einen Index haben, indem sie Primary Key or Unique Constraint .