Temporäre Tabellen vs. Tabellenvariablen in SQL Server

Temporäre Tabellen und Tabellenvariablen nehmen in SQL Server Zwischenergebnisse auf. Beide Varianten wirken auf den ersten Blick ähnlich. Dennoch unterscheiden sie sich bei Lebensdauer, Indizes, Statistiken und Transaktionen. Deshalb kann auch der Ausführungsplan unterschiedlich ausfallen. Welche Lösung passt, hängt von der Datenmenge und der konkreten Abfrage ab.

Das folgende Beispiel ist in SQL Server Management Studio ausführbar. Es erzeugt 100.000 Zeilen und vergleicht beide Varianten mit gleichartigen Indizes. Anschließend kannst du die geschätzten und tatsächlichen Zeilen im Ausführungsplan prüfen. Zusätzlich lassen sich logische Lesevorgänge und Laufzeiten vergleichen.

Temporäre Tabellen und Tabellenvariablen im Überblick

Diese Übersicht bezieht sich auf lokale temporäre Tabellen mit einem Nummernzeichen. Globale temporäre Tabellen mit zwei Nummernzeichen, zum Beispiel ##Tabelle, haben andere Sichtbarkeitsregeln. Sie sind nicht Gegenstand dieses Vergleichs.

Lebensdauer und Sichtbarkeit

Eine lokale temporäre Tabelle wie #Zwischenergebnis ist innerhalb der aktuellen Verbindung sichtbar. Wird sie in einem Batch angelegt, bleibt sie grundsätzlich bis zum Ende der Sitzung bestehen. Du kannst sie vorher auch mit DROP TABLE entfernen.

Legst du die Tabelle in einer gespeicherten Prozedur an, entfernt SQL Server sie nach dem Ende dieser Prozedur. Dadurch kannst du Zwischenergebnisse innerhalb einer Prozedur verwenden, ohne die Tabelle manuell aufzuräumen.

Im Gegensatz dazu deklarierst du eine Tabellenvariable mit DECLARE @Zwischenergebnis TABLE (...). Ihr Gültigkeitsbereich endet mit dem Batch, der Prozedur oder der Funktion, in der sie angelegt wurde.

Außerhalb dieses Bereichs ist die Tabellenvariable nicht verfügbar. Das gilt auch für dynamisches SQL: Eine Variable, die außerhalb von sp_executesql deklariert wurde, ist im dynamisch ausgeführten Batch nicht sichtbar. Deshalb muss die Deklaration gegebenenfalls innerhalb des dynamischen Batches erfolgen.

Beide Varianten können in tempdb gespeichert werden. Eine Tabellenvariable liegt also nicht automatisch nur im Arbeitsspeicher. Vielmehr hängen die interne Verarbeitung und der Speicherbedarf unter anderem von der Datenmenge und den verfügbaren Ressourcen ab.

Indizes und Statistiken

Bei einer temporären Tabelle kannst du Indizes ähnlich wie bei anderen Tabellen anlegen. Das ist vor oder nach dem Befüllen möglich. Außerdem kann SQL Server Statistiken zu Spalten und Indizes erstellen oder aktualisieren.

Diese Statistiken helfen dem Optimizer dabei, die Zahl der Zeilen für einen Filter oder Join einzuschätzen. Wenn zum Beispiel nur wenige Zeilen einen bestimmten Wert enthalten, kann diese Information die Wahl eines passenden Zugriffswegs beeinflussen.

Bei einer Tabellenvariablen legst du Indizes dagegen direkt bei der Deklaration fest. Seit SQL Server 2014 unterstützt die Syntax neben Primärschlüsseln und eindeutigen Einschränkungen auch bestimmte Inline-Indizes. Nach der Deklaration lässt sich die Tabellenvariable jedoch nicht mit einem separaten CREATE INDEX oder ALTER TABLE anpassen.

Hinzu kommt, dass für Tabellenvariablen keine Verteilungsstatistiken zu Spalten geführt werden. Selbst wenn beide Tabellen denselben Index besitzen, kennt der Optimizer deshalb nicht in jedem Fall dieselben Informationen über die Häufigkeit einzelner Werte. Ein Index allein garantiert folglich noch keinen passenden Ausführungsplan.

Auswirkungen auf Ausführungspläne

Eine ungenaue Zeilenschätzung kann den gewählten Plan beeinflussen. Der Optimizer könnte zum Beispiel einen Scan statt einer Indexsuche wählen. Bei Joins kann sich dadurch auch die Reihenfolge oder der Join-Algorithmus ändern.

Bei älteren SQL-Server-Versionen oder einem Datenbank-Kompatibilitätsgrad unter 150 wird die Zeilenzahl einer Tabellenvariablen oft sehr niedrig geschätzt. Traditionell ging der Optimizer dabei häufig von einer Zeile aus. Für größere Datenmengen kann diese Annahme zu ungeeigneten Planentscheidungen führen.

Mit SQL Server 2019 und dem Kompatibilitätsgrad 150 führte Microsoft die verzögerte Kompilierung für Tabellenvariablen ein. Dadurch kann der Optimizer beim ersten Kompilieren die tatsächliche Zeilenzahl berücksichtigen. Verteilungsstatistiken für einzelne Spalten entstehen dadurch allerdings nicht.

Außerdem kann der bei der ersten Ausführung erstellte Plan für spätere Ausführungen wiederverwendet werden. Wenn die Datenmenge stark schwankt, passt dieser Plan möglicherweise nicht zu jedem Aufruf. Temporäre Tabellen bieten dem Optimizer Statistiken und mehr Möglichkeiten bei der Indexgestaltung. Aktualisierte Statistiken oder Schemaänderungen können jedoch auch Neukompilierungen auslösen.

Prüfe deshalb den tatsächlichen Ausführungsplan in SQL Server Management Studio. Aktiviere dazu den tatsächlichen Plan und vergleiche die geschätzten mit den tatsächlichen Zeilenzahlen. Ergänzend zeigen SET STATISTICS IO und SET STATISTICS TIME logische Lesevorgänge und Laufzeiten.

Weitere Hinweise zur Untersuchung von Plänen und Indexnutzung findest du in der Indexanalyse im SQL Server mit Query Store, Ausführungsplänen und DMVs.

Reproduzierbares T-SQL-Beispiel

Das Skript erzeugt eine Quelle mit genau 100.000 Zeilen. Davon gehören 99.000 zur Kategorie 1 und 1.000 zur Kategorie 2. Beide Zieltabellen erhalten einen Clustered Index auf Id und einen Nonclustered Index auf Kategorie.

Anschließend verbindet jede Abfrage die Quelle mit einer der beiden Zieltabellen. Beide Abfragen sollten dasselbe Ergebnis liefern. So kannst du die Pläne und Messwerte vergleichen, ohne eine Variante vorab als schneller einzustufen.

IF OBJECT_ID('tempdb..#Quelle') IS NOT NULL
    DROP TABLE #Quelle;

IF OBJECT_ID('tempdb..#Tabelle') IS NOT NULL
    DROP TABLE #Tabelle;

CREATE TABLE #Quelle
(
    Id int NOT NULL,
    CONSTRAINT PK_Quelle PRIMARY KEY CLUSTERED (Id)
);

;WITH Zahlen AS
(
    SELECT CONVERT(int, 1) AS Id

    UNION ALL

    SELECT Id + 1
    FROM Zahlen
    WHERE Id < 100000
)
INSERT INTO #Quelle (Id)
SELECT Id
FROM Zahlen
OPTION (MAXRECURSION 0);

CREATE TABLE #Tabelle
(
    Id int NOT NULL,
    Kategorie tinyint NOT NULL,
    PRIMARY KEY CLUSTERED (Id)
);

CREATE NONCLUSTERED INDEX IX_Tabelle_Kategorie
    ON #Tabelle (Kategorie);

INSERT INTO #Tabelle (Id, Kategorie)
SELECT
    Id,
    CONVERT(tinyint, CASE WHEN Id <= 99000 THEN 1 ELSE 2 END)
FROM #Quelle;

DECLARE @TabelleVariable TABLE
(
    Id int NOT NULL PRIMARY KEY CLUSTERED,
    Kategorie tinyint NOT NULL,
    INDEX IX_TabelleVariable_Kategorie NONCLUSTERED (Kategorie)
);

INSERT INTO @TabelleVariable (Id, Kategorie)
SELECT
    Id,
    CONVERT(tinyint, CASE WHEN Id <= 99000 THEN 1 ELSE 2 END)
FROM #Quelle;

UPDATE STATISTICS #Tabelle WITH FULLSCAN;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT COUNT_BIG(*) AS Treffer
FROM #Quelle AS q
INNER JOIN #Tabelle AS t
    ON t.Id = q.Id
WHERE t.Kategorie = 2;

SELECT COUNT_BIG(*) AS Treffer
FROM #Quelle AS q
INNER JOIN @TabelleVariable AS t
    ON t.Id = q.Id
WHERE t.Kategorie = 2;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Das erwartete Ergebnis sind jeweils 1.000 Treffer. Je nach SQL-Server-Version, Kompatibilitätsgrad und Systemlast können sich die Ausführungspläne und Messwerte unterscheiden.

Beachte außerdem, dass das Skript die Abfragen nach dem Befüllen der Tabellen misst. Für die Gesamtkosten deiner Anwendung solltest du auch das Erzeugen der Daten, das Anlegen der Indizes und eine mögliche wiederholte Verwendung berücksichtigen.

Transaktionen: ein wichtiger Unterschied bei ROLLBACK

Änderungen an einer temporären Tabelle innerhalb einer Transaktion werden durch ROLLBACK zurückgesetzt. Bei einer Tabellenvariablen gilt dieses Verhalten nicht: Ihre Änderungen bleiben nach dem Rollback erhalten.

Das folgende Beispiel zeigt den Unterschied. Die temporäre Tabelle enthält nach dem Rollback keine Zeile mehr. In der Tabellenvariablen bleibt die eingefügte Zeile dagegen bestehen.

IF OBJECT_ID('tempdb..#Transaktion') IS NOT NULL
    DROP TABLE #Transaktion;

CREATE TABLE #Transaktion
(
    Id int NOT NULL
);

DECLARE @Transaktion TABLE
(
    Id int NOT NULL
);

BEGIN TRANSACTION;

INSERT INTO #Transaktion (Id)
VALUES (1);

INSERT INTO @Transaktion (Id)
VALUES (1);

ROLLBACK TRANSACTION;

SELECT COUNT(*) AS ZeilenInTempTable
FROM #Transaktion;

SELECT COUNT(*) AS ZeilenInTabellenvariable
FROM @Transaktion;

Das erste Ergebnis ist 0, das zweite 1. Wenn deine Logik erwartet, dass ein Rollback auch den Inhalt einer Tabellenvariablen entfernt, musst du die Zeilen anschließend selbst löschen. Gerade bei einer Fehlerbehandlung ist dieser Unterschied wichtig.

Entscheidungshilfe für kleine und größere Datenmengen

Eine feste Grenze zwischen kleinen und großen Datenmengen gibt es nicht. Microsoft nennt mehr als 100 erwartete Zeilen als Anlass zur Vorsicht bei Tabellenvariablen. Dieser Wert ist jedoch eine Orientierung und keine allgemeingültige Leistungsgrenze.

Auch der Kompatibilitätsgrad, die Datenverteilung und die Anzahl der Joins spielen eine Rolle. Deshalb solltest du die Entscheidung mit repräsentativen Daten und der tatsächlichen Abfrage überprüfen.

Als Ausgangspunkt kann eine Tabellenvariable für kleine, einfach verarbeitete Zwischenergebnisse übersichtlich sein. Wenn du hingegen Statistiken, flexibel erstellte Indizes oder verlässliche Schätzungen für größere Joins benötigst, ist eine temporäre Tabelle oft einen Test wert.

Fazit

Temporäre Tabellen und Tabellenvariablen unterscheiden sich bei Lebensdauer, Indexgestaltung, Statistiken und Rollback-Verhalten. Seit SQL Server 2019 kann die verzögerte Kompilierung die Zeilenschätzung für Tabellenvariablen verbessern. Sie ersetzt jedoch keine Spaltenstatistiken.

Welche Variante besser passt, zeigt der tatsächliche Ausführungsplan unter realistischen Bedingungen. Miss daher beide Ansätze mit repräsentativen Daten, bevor du eine Leistungsentscheidung triffst.

Wenn du Zwischenergebnisse in ein sauberes Datenmodell einordnen möchtest, hilft der Beitrag Normalisierung von Datenbanken bis zur 3. Normalform. Weitere Anleitungen findest du in der Kategorie MS-SQL.

Weiterführende technische Quellen

• Microsoft Learn: Tabellenvariablen, Einschränkungen und Indizes
• Microsoft Learn: Verzögerte Kompilierung für Tabellenvariablen
• Microsoft Learn: Statistiken im SQL Server
• Microsoft Learn: Verhalten von ROLLBACK bei Tabellenvariablen

Werbung / Affiliate-Links: Die mit einem Sternchen (*) gekennzeichneten Links sind Amazon-Partnerlinks. Wenn du darüber etwas kaufst, erhalte ich möglicherweise eine Provision. Für dich entstehen dadurch keine zusätzlichen Kosten. Als Amazon-Partner verdiene ich an qualifizierten Verkäufen.