Wann sollte ich eine Tabellenvariable vs temporäre Tabelle in SQL Server verwenden?

Ihre Frage zeigt, dass Sie erlag einige der häufigsten Missverständnissen im Zusammenhang mit table-Variablen und temporäre Tabellen.

Ich geschrieben habe eine Recht umfassende Antwort auf der DBA-Website Blick auf die Unterschiede zwischen den beiden Objekttypen. Auch diese Adressen Ihre Frage über CD vs Speicher (ich sehe keinen signifikanten Unterschied im Verhalten zwischen den beiden).

Bezüglich der Frage im Titel, aber, wenn, eine Tabelle zu verwenden variable vs eine lokale temporäre Tabelle, die Sie haben nicht immer die Wahl. In Funktionen, zum Beispiel, ist es nur möglich, eine Tabelle zu verwenden Variablen und wenn Sie schreiben müssen, um die Tabelle in einen untergeordneten Bereich dann nur ein #temp Tabelle tun
(Tabellenwert-Parameter erlauben readonly-Zugriff).

Wo Sie die Wahl haben, einige Vorschläge sind unten (obwohl die zuverlässigste Methode ist, einfach test sowohl mit Ihrer spezifischen workload).

1. Wenn Sie brauchen, ein index kann nicht erstellt werden implizit durch eine UNIQUE oder PRIMARY KEY Einschränkung, dann müssen Sie einen #temporary Tabelle, da es nicht möglich ist, diese auf table-Variablen. (Beispiele für solche Indizes sind nicht eindeutig diejenigen, gefilterte Indizes oder Indizes mit INCLUDEd-Spalten). NB: SQL Server 2014 wird, dass nicht-eindeutige Indizes erklärt werden inline für table-Variablen.
2. Wenn Sie wiederholt das hinzufügen und löschen von großen Nummern der Zeilen aus der Tabelle, dann mit einem #temporary Tabelle. Unterstützt TRUNCATE (das ist effizienter als DELETE für große Tabellen) und zusätzlich nachfolgende Einfügungen nach einer TRUNCATE können, haben eine bessere performance als diejenigen, die nach einer DELETE wie hier abgebildet.
3. Wenn Sie löschen oder aktualisieren einer großen Anzahl von Zeilen, dann werden die temp-Tabelle kann auch viel mehr leisten als eine table-variable - wenn Sie in der Lage zu verwenden rowset-sharing (siehe "Auswirkungen der rowset-sharing" unten für ein Beispiel).
4. Wenn die optimalen plan mit der Tabelle variieren, abhängig von Daten, die dann mit einem #temporary Tabelle. Das unterstützt die Erstellung von Statistiken, der ermöglicht, den plan dynamisch neu kompiliert nach den Daten (obwohl für zwischengespeicherte temporäre Tabellen in einer gespeicherten Prozedur die Neukompilierung Verhalten verstanden werden muss separat bestellt werden).
5. Wenn die optimalen plan für die Abfrage mit der Tabelle ist unwahrscheinlich, dass jemals ändern, dann können Sie erwägen, eine table-variable, überspringen Sie den Aufwand der Erstellung von Statistiken und Kompilierungen (würde eventuell benötigen Sie Hinweise zum beheben der plan, den Sie möchten).
6. Wenn die Quelle für die Daten in die Tabelle eingefügt wird, ist von einer potenziell teuer SELECT Aussage dann noch bedenkt, dass die Verwendung einer Tabelle für eine variable-block die Möglichkeit, dies unter Verwendung eines parallelen plans.
7. Wenn Sie die Daten in der Tabelle, um zu überleben, wird ein rollback von einer äußeren user transaction verwenden Sie dann eine table-variable. Ein möglicher Anwendungsfall könnte sein, die Protokollierung der Fortschritte der verschiedenen Schritte in eine lange SQL-batch.
8. Bei Verwendung eines #temp Tabelle innerhalb einer Benutzer-Transaktion sperren gehalten werden kann, länger als für table-Variablen (möglicherweise bis zum Ende der Transaktion vs. Ende-Anweisung abhängig von der Art der Sperre und isolation-level) und es kann auch verhindern, dass die Kürzung des tempdb Transaktionsprotokoll, bis der Benutzer die Transaktion endet. So könnte dies begünstigen die Verwendung von table-Variablen.
9. In gespeicherten Routinen, sowohl für table-Variablen und temporären Tabellen zwischengespeichert werden können. Die Metadaten-Pflege für die Cache-Tabelle Variablen ist weniger, als für #temporary Tabellen. Bob Ward weist in seinem tempdb Präsentation , dies kann zu zusätzlichen Konflikten auf system-Tabellen unter den Bedingungen der hohen Parallelität. Darüber hinaus beim Umgang mit kleinen Mengen von Daten kann einen messbaren Unterschied bei der Leistung.

Auswirkungen von rowset-sharing

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

InformationsquelleAutor der Antwort Martin Smith

Microsoft hier sagt

Tabelle Variablen, die nicht über die Statistiken, die Sie nicht auslösen neu kompiliert. Daher wird in vielen Fällen der Optimierer eine Abfrage erstellen, die plan auf der Annahme, dass die table-variable keine Zeilen. Aus diesem Grund, Sie sollten vorsichtig sein, über eine table-variable, wenn Sie erwarten, dass eine größere Anzahl von Zeilen (mehr als 100). Temp-Tabellen kann eine bessere Lösung in diesem Fall.

InformationsquelleAutor der Antwort Paul Sturm

Variable Tabelle ist nur für die aktuelle Sitzung, zum Beispiel, wenn Sie brauchen, um EXEC einer anderen gespeicherten Prozedur in der aktuellen eine, die Sie haben, um die Tabelle als Table Valued Parameter - und natürlich wird dies Auswirkungen auf die Leistung, mit temporäre Tabellen Sie können dies tun, mit nur übergeben die temporäre Tabelle namens

Zu testen, eine Temporäre Tabelle:

• Öffnen von management studio-Abfrage-editor
• Erstellen Sie eine temporäre Tabelle
• Öffnen Sie eine Abfrage-editor-Fenster
• Wählen Sie aus dieser Tabelle "Verfügbar"

Zu testen, eine Variable Tabelle:

• Öffnen von management studio-Abfrage-editor
• Erstellen Sie eine Variable Tabelle
• Öffnen Sie eine Abfrage-editor-Fenster
• Wählen Sie aus dieser Tabelle "Nicht Verfügbar"

Etwas, was ich erlebt habe, ist: Wenn Sie in Ihrem schema nicht GRANT Berechtigung zum erstellen von Tabellen verwenden Sie dann die variable Tabellen. Außerdem werden die temporären Tabellen befindet sich in der tempdb-Datenbank und die Tabelle Variablen kann sich auf die beiden Speicher und die Festplatte der tempdb-Datenbank. Siehe hier: https://www.linkedin.com/pulse/highlighted-differences-between-sql-server-temporary-tables-andrew

InformationsquelleAutor der Antwort Mina Gabriel