Welche Strategie ist am besten geeignet, um Daten, die temporär für weitere Aufgaben benötigt werden, zu speichern und zu verwalten? Mit Microsoft SQL Server 2000 wurden zum ersten Mal Tabellenvariablen als Erweiterung eingeführt. Die Arbeit mit temporären Tabellen war zu diesem Zeitpunkt bereits Alltag und jeder Datenbankprogrammierer hat diese bewährte Technik verwendet. Mit der Einführung von Tabellenvariablen wird alles besser – so dachte man zumindest. Dieser Artikel zeigt die Unterschiede zwischen beiden technischen Möglichkeiten.

Temporäre Tabellen

Temporäre Tabellen sind, wie der Name es bereits sagt, “temporär”. Temporäre Tabellen unterscheiden sich technisch nicht durch normale Tabellen, die sich in den Datenbanken befinden. Temporäre Tabellen können jederzeit erstellt und gelöscht werden. Daten können in temporären Tabellen beliebig hinzugefügt, geändert oder gelöscht werden. Der Unterschied zu “normalen” Relationen besteht in zwei wesentlichen Punkten, die nachfolgend etwas näher beleuchtet werden sollen.

Temporäre Tabellen befinden sich in der TEMPDB

Die Datenbank TEMPDB ist eine Systemdatenbank von SQL Server, die für JEDEN Benutzer einer Datenbank verfügbar ist. In dieser globalen Systemdatenbank werden alle temporären Benutzerobjekte angelegt. Diese Datenbank wird bei jedem Start von Microsoft SQL Server neu angelegt und beginnt somit mit einer “sauberen Arbeitsfläche”. Temporäre Relationen unterscheiden sich durch folgende Merkmale von normalen Relationen:

• Temporäre Tabellen beginnen IMMER mit “#” (lokal) oder “##” (global)!
• Temporäre Tabellen werden automatisch gelöscht, sobald sie nicht mehr verwendet werden!
• Globale temporäre Relationen sind für ALLE Datenbanksitzungen verfügbar!

Lokale temporäre Tabellen

Lokale temporäre Tabellen sind nur innerhalb der Datenbanksitzung gültig, in der sie erstellt wurde. Lokale temporäre Tabellen beginnen immer mit einem Hashtag (“#”). Wird die Datenbankverbindung getrennt, werden lokale temporäre Objekte, die während dieser Datenbanksitzung erstellt wurden, automatisch gelöscht.

-- Gültigkeitsbereich von temporären Tabellen
USE tempdb;
GO

-- Erstellen von zwei Testbenutzern in TEMPDB
CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
GO

-- Im Kontext von Benutzer User 1 ausführen
EXECUTE AS USER = 'User1'
     CREATE TABLE #User1_Table
     (
         Id int NOT NULL IDENTITY,
         spid int NOT NULL DEFAULT (@@spid),
         myText varchar(100) NOT NULL DEFAULT (suser_sname())
     )
     GO

     INSERT INTO #User1_Table DEFAULT VALUES
     GO 10

     SELECT * FROM #User1_Table
REVERT
GO

-- Ausführen als User2
EXECUTE AS USER = 'User2'
    SELECT * FROM #User1_Table;
REVERT
GO

Wird der obige Code in EINEM Ausführungsfenster von Microsoft SQL Server Management Studio ausgeführt, kann die vom ersten Benutzer (“User1”) erstellte temporäre Tabelle auch von Benutzer 2 (“User2”) verwendet werden. Die Demonstration zeigt verschiedene Besonderheiten von temporären Tabellen, die bei der Wahl der geeigneten Technologie zu berücksichtigen sind:

• Lokale temporäre Tabellen sind nicht auf einen Ersteller begrenzt
• Lokale temporäre Tabellen sind nur auf die Verbindung beschränkt

Wird das zweite Codeelement (die Ausführung als “User2”) in einem neuen Abfragefenster von Microsoft SQL Server ausgeführt, wird eine Fehlermeldung ausgelöst, die besagt, dass eine temporäre Tabelle mit dem Namen [#User1_Table] nicht vorhanden ist.

Msg 208, Level 16, State 0,
Line 4 Ungültiger Objektname '#User1_Table'.

Globale temporäre Tabellen

Globale temporäre Tabellen weisen im Unterschied zu lokalen temporären Relationen als erste Zeichen im Namen zwei Hashtags („##“) auf. Eine globale temporäre Tabelle ist für JEDE Sitzung sichtbar. Globale temporäre Tabellen werden gelöscht, wenn ALLE Sitzungen, in denen auf diese Tabelle verwiesen wurde, die Verbindung mit der Instanz von SQL Server getrennt haben. Verwendet man obiges Script und ändert man die Definition der temporären Tabelle zu einer globalen Tabelle, kann in einer zweiten Session ohne Probleme auf diese Tabelle und deren Daten zugegriffen werden. Dieses Verhalten zeigt einen Schwachpunkt am Konzept der temporären Relationen:

• Globale temporäre Tabellen können von allen Datenbankbenutzern uneingeschränkt verwendet werden.
• Es gibt KEINE Möglichkeit, diese Berechtigungen mittels DENY oder REVOKE einzuschränken.
• Globale temporäre Tabellen werden gelöscht, wenn keine Session mehr existiert, die auf die temporäre Tabelle zugegriffen hat

Aus diesen Gründen gilt es, bereits im Vorfeld eines Einsatzs von globale Tabellen eine Risikobewertung durchzuführen. Bei sensiblen und vertraulichen Daten sollte möglichst auf die Verwendung von globalen temporären Relationen verzichtet werden!

Temporäre Tabellen und Indexierung

Temporäre Tabellen verhalten sich wie “gewöhnliche” Datenbanktabellen. Das schließt ein, dass sie indiziert werden können und das Statistiken gepflegt werden. Der folgende Beispielcode erstellt eine temporäre Tabelle mit 1.010 Datensätzen. Anschließend wird die Tabelle indexiert und eine Abfrage auf den Index ausgeführt.

USE tempdb;
GO

-- Erstellen einer Demo-Tabelle
CREATE TABLE ##customer_country
(
    customer_id INT	    NOT NULL   IDENTITY (1, 1),
    CCode       CHAR(3)	    NOT NULL,
    c1	        CHAR(2000)  NOT NULL   DEFAULT ('just a filler')
);

-- Erstellen eines Clustered Index auf Customer_Id
CREATE UNIQUE CLUSTERED INDEX cix_customer_Id ON ##customer_country (customer_id);

-- Erstellen eines Index auf CCode
CREATE INDEX ix_CCode ON ##customer_country (CCode);

-- 1000 Datensätze für Deutschland sowie 10 Datensätze für Österreich eintragen
SET NOCOUNT ON;
GO

INSERT INTO ##customer_country(CCode) VALUES  ('DE');
GO 1000

INSERT INTO ##customer_country(CCode) VALUES ('AT');
GO 10

SET NOCOUNT OFF;
GO
SELECT * FROM ##customer_country WHERE CCode = 'AT';
SELECT * FROM ##customer_country WHERE CCode = 'DE';

Der Ausführungsplan zeigt, dass für beide Abfragen unterschiedliche Strategien (und Indexe) verwendet werden.
Vorsicht ist angeraten, wenn innerhalb einer Stored Procedure eine temporäre Tabelle erstellt und nachträglich Metadaten der Tabelle geändert werden! Das folgende Beispiel verdeutlicht das Problem. Innerhalb der Stored Procedure wird der Clustered Index mit Hilfe eines Constraints (Primary Key) erstellt und im Anschluss wird ein zusätzlicher non clustered Index erstellt.

CREATE PROC dbo.demo
AS
    SET NOCOUNT ON;
    DECLARE @i INT = 1;

    -- Erstellen der temporären Tabelle
    CREATE TABLE #master_table
    (
        Id       int      NOT NULL  IDENTITY(1, 1),
        Customer char(89) NOT NULL  DEFAULT ('a new customer'),
        c2       DATE     NOT NULL  DEFAULT (GETDATE())
    );

    -- Eintragen von 100 Datensätzen
    WHILE @i <= 100
    BEGIN
        INSERT INTO #master_table DEFAULT VALUES;
        SET @i += 1;
    END

    -- Provokation eines RECOMPILE, da Metadaten der Tabelle geändert werden!
    ALTER TABLE #master_table ADD CONSTRAINT pk_master_table PRIMARY KEY CLUSTERED (Id);

    -- Erstellung eines nonclustered Index ohne RECOMPILE!
    CREATE INDEX ix_master_table ON #master_table (c2);

    -- Auswahl eines beliebigen Datensatzes
    SELECT * FROM #master_table AS MT WHERE Id = 10;

    -- Löschen der temporären Tabelle
    DROP TABLE #master_table;

    SET NOCOUNT OFF;
GO

In einem Profiler Trace wird während der Ausführung der Prozedur protokolliert, wann ein RECOMPILE stattfindet.

Die Abbildung zeigt, innerhalb der Prozedur jedes Mal ein RECOMPILE ausgelöst wird, wenn die ALTER TABLE-Zeile ausgeführt wurde. Hierbei handelt es sich um eine Schemaänderung die zur Folge hat, dass ursprüngliche – mögliche – Ausführungspläne im Zusammenhang mit der temporären Tabelle verworfen werden müssen. Ebenfalls erkennbar ist, dass das Hinzufügen eines Indexes nicht automatisch zu einem RECOMPILE führt; ein neuer Index bedeutet nicht, dass sich Metadaten der Tabelle selbst ändern!

Temporäre Tabelle und Statistiken

Statistiken sind für adäquate Ausführungspläne unerlässlich. Wenn Microsoft SQL Server keine oder veraltete Statistiken zu einem Index besitzt, kann sich das negativ auf die Ausführung von Abfragen auswirken. Das folgende Beispiel zeigt den Zusammenhang zwischen – notwendigen – Statistiken und geeigneten Ausführungsplänen.

-- Erstellen der temporären Tabelle
CREATE TABLE ##customer_country
(
   customer_id INT        NOT NULL   IDENTITY(1, 1),
   CCode       CHAR(3)    NOT NULL,
   C1          CHAR(2000) NOT NULL   DEFAULT ('only a filler')
);
GO

SET NOCOUNT ON;
GO

-- 1.000 Datensätze für Deutschland eintragen
INSERT INTO ##customer_country (CCode) VALUES ('DE');
GO 1000

-- 10 Datensätze für Österreich eintragen
INSERT INTO ##customer_country (CCode) VALUES ('AT');
GO 10

-- Erstellung des Clustered Index ...
CREATE UNIQUE CLUSTERED INDEX cix_customer_id ON ##customer_country (customer_id);

-- Erstellen des non clustered index auf CCode
CREATE INDEX ix_CCode ON ##customer_country (CCode);
GO

Zunächst wird eine temporäre Tabelle erstellt und insgesamt 1.010 Datensätze mit einer unterschiedlichen Verteilung von Daten (CCode) hinzugefügt. Basierend auf Statistiken kann Microsoft SQL Server die geeigneten Ausführungspläne für die jeweiligen Abfragen nach den unterschiedlichen Länderkennzeichen ermitteln und umsetzen:

-- Auswahl aller Kunden in Österreich!
SELECT * FROM #customer_country AS CC WHERE CCode = 'AT';

-- Auswahl aller Kunden in Deutschland!
SELECT * FROM #customer_country AS CC WHERE CCode = 'DE';

Die Abbildung zeigt verschiedene Ausführungspläne. Eine Abfrage nach dem Länderkennzeichen “AT” führt zu einem INDEX SEEK mit einer Schlüsselsuche. Für Microsoft SQL Server ist diese Variante die effektivste Variante, da Microsoft SQL Server “weiß”, dass es nur 10 Datensätze sind. Die zweite Abfrage nach dem Länderkennzeichen “DE” ist kostenmäßig für einen INDEX SEEK zu teuer. Microsoft SQL Server kann auf Grund der bekannten Zeilenzahl von 1.000 Datensätzen das erforderliche IO berechnen und entscheidet sich für einen INDEX SCAN!

Temporäre Tabellen und Transaktionen

Temporäre Tabellen verhalten sich in konkurrierenden Systemen wie herkömmliche Tabellen. Wie bereits weiter oben erwähnt, gibt es lokale und globale temporäre Tabellen. Während lokale temporäre Tabellen nur innerhalb einer Session sichtbar sind, verhält es sich mit globalen temporären Tabellen anders. Dieser Umstand birgt Gefahren in Form von Sperren, die in einer Applikation auftreten können. Im vorherigen Beispiel wurde eine globale temporäre Tabelle verwendet. Wird nun in einer Session ein Update auf Zeilen in der Tabelle ausgeführt, wird auf diese Zeilen eine Sperre gesetzt:

-- Transaktion beginnen
BEGIN TRANSACTION Demo;
    -- Aktualisierung aller Länderkennzeichen von Österreich
    UPDATE ##customer_country
           SET CCode = 'FR'
    WHERE  CCode = 'AT';

    -- Welche Sperren wurden auf das Objekt gesetzt
    SELECT  I.name                      AS index_name,
            DTL.resource_type,
            DTL.resource_description,
            DTL.request_mode
    FROM    sys.dm_tran_locks AS DTL INNER JOIN sys.partitions AS P
            ON P.hobt_id = DTL.resource_associated_entity_id INNER JOIN sys.indexes AS I
            ON I.OBJECT_ID = P.OBJECT_ID AND I.index_id = P.index_id
    WHERE   DTL.resource_database_id = DB_ID() AND
            DTL.request_session_id = @@SPID
    ORDER BY
            request_session_id,
            resource_associated_entity_id 

    -- Transaktion bleibt geöffnet und in einer zweite Session wird versucht
    -- auf die Ressourcen mittels SELECT zuzugreifen
ROLLBACK TRANSACTION;

In der Abbildung ist zu erkennen, dass sowohl Teile des Clustered Index [cix_customer_id] als auch des Index auf das Feld CCode von Microsoft SQL Server blockiert werden. Auf die Datensätze selbst (KEY) wird eine eXklusive Sperre gesetzt. Die Intent eXclusive Sperren dienen nur einer möglichen Lock-Eskalation”. Wird in einer zweiten Sitzung versucht, auf die Daten der Kunden aus dem Land “AT” zuzugreifen, werden so lange keine Daten geliefert, bis die Sperren der aktualisierenden Transaktion wieder aufgehoben werden.

Temporäre Tabellen und Collation

Eine der größten Herausforderungen für Datenbankprogrammierer ist bei Verwendung von temporären Tabellen die Berücksichtigung der eingestellten Collation für Datenbanken und Tabellen. Die Herausforderung besteht darin, dass – ohne explizite Angabe einer Collation – immer die Einstellung der Datenbank TEMPDB verwendet wird. Das folgende Szenario verdeutlicht den Sachverhalt:

-- Welche Collation gibt es für TEMPDB und Server?
SELECT name                        AS DatabaseName,
       collation_name              AS DatabaseCollation,
       SERVERPROPERTY('Collation') AS ServerCollation
FROM   sys.databases AS D
WHERE  D.database_id = 2;

Mit Hilfe des obigen Codes werden Informationen über die aktuelle Collation von Server und TEMPDB abgerufen. Da es sich bei TEMPDB um eine Systemdatenbank handelt, sind die Einstellungen identisch.

-- Erstellung einer neuen Datenbank mit unterschiedlicher Collation!
CREATE DATABASE [demo_db]
COLLATE Latin1_General_BIN;

Im Anschluss wird eine neue Datenbank [demo_db] erstellt. Diese Datenbank verwendet eine andere Collation als den Serverstandard.

-- Erstellen einer Tabelle in demo_db mit Standardeinstellungen
USE demo_db;
GO

CREATE TABLE dbo.Customers
(
   Customer_Id	    INT          NOT NULL IDENTITY (1, 1),
   Customer_Number  CHAR(5)      NOT NULL,
   Customer_Name    VARCHAR(255) NOT NULL,
	
   CONSTRAINT pk_Customers_Id PRIMARY KEY CLUSTERED (Customer_Id)
);
GO

-- Zusätzlicher Index auf Customer_Number
CREATE UNIQUE INDEX ix_Customer_Number ON dbo.Customers(Customer_Number)
INCLUDE (Customer_Name);

-- Eintragen von 3 Datensätzen
INSERT INTO dbo.Customers (Customer_Number, Customer_Name)
VALUES
('A0001', 'db Berater GmbH'),
('A0092', 'ABC GmbH'),
('B2345', 'ZYX AG');
GO

In er neuen Datenbank wird eine Tabelle mit 3 Datensätzen angelegt. Da keine explizite Collation für die Textattribute angegeben wurden, wird die Collation der Datenbank [demo_db] übernommen.

-- Information über die Metadaten der Tabelle dbo.Customers
SELECT	OBJECT_NAME(C.object_id)	AS Table_Name,
	C.name				AS Column_Name,
	S.name				AS Type_Name,
	C.column_id,
	C.max_length,
	C.collation_name
FROM	sys.columns AS C INNER JOIN sys.types AS S
	ON (C.system_type_id = S.system_type_id)
WHERE   object_id = object_id('dbo.Customers', 'U');

Der nachfolgende Code erstellt eine temporäre Tabelle und eine Kundennummer wird als Suchkriterium eingetragen. Anschließend wird die Tabelle [dbo].[Customer] mit dieser temporären Tabelle mittels JOIN verbunden und die Abfrage ausgeführt.

-- Erstellen einer temporären Tabelle
CREATE TABLE #t (Customer_Number CHAR(5) PRIMARY KEY CLUSTERED);
GO

-- Auswahl aus dem Frontend wird in temporärer Tabelle gespeichert
INSERT INTO #t (Customer_Number) VALUES ('A0001');
GO

-- Auswahl von Datensätzen aus Kundentabelle
SELECT C.*
FROM   dbo.Customers AS C INNER JOIN #t AS CN
       ON (C.Customer_Number = CN.Customer_Number);

Die Auswahl führt zu einem klassischen Fehler, der darauf hinweist, dass die Collation beider Verbindungsfelder nicht übereinstimmen.

Meldung 468, Ebene 16, Status 9, Zeile 3
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN"
in the equal to operation.

Tabellenvariablen

Tabellenvariablen wurden mit Microsoft SQL Server 2000 erstmals eingeführt. Mit dem Einzug von Tabellenvariablen in die Programmiersprache von Microsoft SQL Server sollten viele Dinge vereinfacht.Während temporäre Tabellen während einer ganzen Session gültig sind, beschränkt sich die Gültigkeit von Tabellenvariablen immer auf den BATCH! Einige Vor- und Nachteile als auch hartnäckige Mythen rund um Tabellenvariablen werden nachfolgend beschrieben.

Gültigkeit von Tabellenvariablen

Tabellenvariablen sind nur innerhalb eines Batches gültig. Das bedeutet für die Programmierung, dass in dem Moment, in dem ein GO den Batch abschließt, die Tabellenvariable ihre Gültigkeit verliert. Hier unterscheidet sich die Tabellenvariable nicht von einer herkömmlichen Variable, wie der nachfolgende Code zeigt:

-- Definition einer Tabellenvariablen
DECLARE @customer_country TABLE
(
    customer_id  INT        NOT NULL IDENTITY (1, 1),
    CCode        CHAR(3)    NOT NULL,
    c1           CHAR(2000) NOT NULL DEFAULT ('just a filler')
);

-- 100 Datensätze aus Deutschland
DECLARE @I INT = 1;
WHILE @I <= 100
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('DE')
    SET @I += 1;
END

-- Anzeige aller Datensätze IM Batch!
SELECT * FROM @customer_country AS CC;
GO

-- Batch ist beendet und Tabellenvariable ist nicht mehr gültig
SELECT * FROM @customer_country AS CC;
GO

Wenn der obige Code vollständig ausgeführt wird, läuft das erste SELECT einwandfrei, da es sich innerhalb des Batches befindet. Nach dem GO werden erneut Daten aus der Tabellenvariable angefordert. Da jedoch mittels GO der vorherige Batch abgeschlossen ist, wird die Tabellenvariable nicht erkannt.

Tabellenvariablen und Indexierung

Bis Microsoft SQL Server 2012 waren Indexe in Tabellenvariablen nicht möglich. Sofern man eine “Indexierung” wünschte, konnte man sich nur über den Umweg eines “Constraints” helfen. Der nachfolgende Code zeigt, wie innerhalb einer Tabellenvariable bis SQL Server 2012 Indexe erstellt werden konnten.

DECLARE @customer_country TABLE
(
    customer_id INT        NOT NULL  IDENTITY (1, 1),
    CCode       CHAR(3)    NOT NULL,
    c1          CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    UNIQUE ix_ccode (CCode)
);

Seit Microsoft SQL Server 2014 ist diese Einschränkung jedoch aufgehoben und man kann nun auch non clustered Indexe innerhalb der Tabellendefinition deklarieren.

DECLARE @customer_country TABLE
(
    customer_id INT        NOT NULL  IDENTITY (1, 1),
    CCode       CHAR(3)    NOT NULL,
    c1          CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    INDEX ix_ccode NONCLUSTERED (CCode)
);

Auf Grund der restriktiven Regeln für Tabellenvariablen ist kein Risiko der Neukompilierung einer Stored Procedure vorhanden. Für Tabellenvariablen gilt:

• Tabellenvariablen sind nur im aktuellen Batch gültig
• Die vollständige Tabelle muss im DECLARE-Block beschrieben werden
  Zusätzliche Indexe oder Attribute sind nach der Deklaration nicht mehr möglich
• Es wird die COLLATION der aktuellen Datenbank für Textdatentypen verwendet
• TRUNCATE wird nicht unterstützt (DDL-Befehl)

Da außerhalb der Deklaration einer Tabellenvariable keine zusätzlichen Änderungen an der Tabellenvariable erlaubt sind, ist eine Rekompilierung – sofern sie nicht explizit angegeben wird – ausgeschlossen.

Tabellenvariablen und Statistiken

Statistiken werden für temporäre Tabellenvariablen NICHT gepflegt. Diese Einschränkung kann bei der Wahl von Tabellenvariablen ein echter Performancekiller werden. Als Beispiel soll folgende Prozedur (Microsoft SQL Server 2014) dienen:

-- Deklaration der Tabellenvariable
DECLARE @customer_country TABLE
(
    customer_id  INT        NOT NULL  IDENTITY (1, 1),
    CCode        CHAR(3)    NOT NULL,
    c1           CHAR(2000) NOT NULL  DEFAULT ('just a filler'),

    PRIMARY KEY CLUSTERED (customer_id),
    INDEX ix_ccode NONCLUSTERED (CCode)
);

-- 1.000 Datensätze aus Deutschland
DECLARE @I INT = 1;
WHILE @I <= 1000
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('DE')
    SET @I += 1;
END

-- 10 Datensätze aus Österreich
DECLARE @I INT = 1;
WHILE @I <= 10
BEGIN
    INSERT INTO @customer_country(CCode) VALUES ('AT')
    SET @I += 1;
END

-- Abfrage nach Datensätzen aus Deutschland!
SELECT * FROM @customer_country AS CC WHERE CCode = 'DE';

-- Abfrage nach Datensätzen aus Österreich!
SELECT * FROM @customer_country AS CC WHERE CCode = 'AT';

Im Code wird zunächst eine Tabellenvariable deklariert, die mit 1.000 Datensätzen aus Deutschland und 10 Datensätzen aus Österreich gefüllt wird. Bei der Auswahl der Datensätze aus Deutschland – wie auch aus Österreich – werden jedoch die gleichen Ausführungspläne verwendet!

Die Abbildung zeigt, dass Microsoft SQL Server davon ausgeht, dass nur eine Zeile in der Tabelle steht. Somit ist für Microsoft SQL Server ein INDEX SCAN über den Clustered Index optimaler als ein INDEX SEEK über den Index ix_ccode und eine weitere Schlüsselsuche. Das Problem in diesem Beispiel liegt auf der Hand; Microsoft SQL Server wird NIE den Index verwenden, der auf dem Attribut CCode liegt. Microsoft SQL Server geht grundsätzlich von einem Datensatz aus, der geliefert werden soll. Somit wird einem INDEX SCAN der Vorzug gegeben. Die Ausführung der Abfrage nach Kunden aus Österreich belegt diese Vermutung.

Noch deutlicher wird die “Nichtverwendung” von Statistiken mit dem nachfolgenden Skript. Die Verwendung der Traceflags bewirkt eine Ausgabe der von Microsoft SQL Server geprüften und verwendeten Statistiken für die Abfrage.

SET NOCOUNT ON;
GO

DECLARE	@t TABLE
(
    Id  INT         NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000)  NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE        NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

DECLARE @i INT = 1
WHILE @i <= 1000
BEGIN
    INSERT INTO @t DEFAULT VALUES;
    SET @i += 1;
END


SELECT * FROM @t AS T
WHERE T.Id = 100
OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,
    QUERYTRACEON 9292,
    QUERYTRACEON 9204
);

Mit Traceflag 9292 werden die Statistiken angezeigt, die für den Query Optimizer von Microsoft SQL Server als „interessant“ für den Ausführungsplan eingestuft werden; Traceflag 9204 zeigt, welche Statistiken der Query Optimizer von Microsoft SQL Server vollständig lädt, um einen Ausführungsplan zu generieren. Das Ergebnis ist LEER!

Tabellenvariablen und Transaktionen

Im Gegensatz zu Temporären Tabellen sind Tabellenvariablen nicht transaktionsgebunden. Wird der Wert einer Datenzeile in einer Tabellenvariable innerhalb einer expliziten Transaktion geändert, wird der ursprüngliche Wert bei einem möglichen ROLLBACK nicht wiederhergestellt. Tabellenvariablen speichern UPDATES unabhängig von einer expliziten Transaktion.

DECLARE @t TABLE
(
    Id  INT         NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000)  NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE        NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

DECLARE @i INT = 1
WHILE @i <= 10
BEGIN
   INSERT INTO @t DEFAULT VALUES;
   SET @i += 1;
END

BEGIN TRANSACTION
    UPDATE @t
    SET    c1 = 'AT'
    WHERE  Id = 1;
ROLLBACK TRANSACTION

SELECT * FROM @t;
GO

Der Vorteil dieses Verfahrens ist natürlich naheliegend; da Tabellenvariablen nicht transaktionsgebunden sind, benötigen sie keine Sperren und müssen kein Transaktionsprotokoll führen – sie sind bei DML-Operationen schneller!

Verwendung von Tabellenvariablen

Bei den vielen Nachteilen, die Tabellenvariablen – offensichtlich – haben, stellt man sich die Frage, wozu man Tabellenvariablen verwenden sollte. Ein sehr großer Vorteil von Tabellenvariablen ist, dass man sie wie ein Array an Funktionen und Stored Procedures übergeben kann. Man kann also zunächst Werte in eine Tabellenvariable füllen um sie anschließend in einer Stored Procedure zu verarbeiten. Der folgende Code zeigt ein mögliches Szenario, das mit Hilfe von Tabellenvariablen gelöst werden kann.
“In einer Applikation können aus einer Liste von Kunden mehrere Kunden ausgewählt werden, um sie anschließend in einem Report auszugeben”

-- Zunächst wird ein neuer Datentyp TABLE angelegt
CREATE TYPE Customer_Id AS TABLE (customer_id int NOT NULL PRIMARY KEY CLUSTERED);
GO

-- Erstellung einer Prozedur für die Auswahl von Kunden
-- Der zu übergebende Datentyp ist der zuvor definierte TYPE
CREATE PROC dbo.proc_CustomerList
	@C AS Customer_Id READONLY
AS
    SET NOCOUNT ON;

    SELECT * FROM dbo.Customer
    WHERE  Customer_Id IN (SELECT Customer_Id FROM @c);

    SET NOCOUNT OFF;
GO

-- Erstellung einer Tabelle vom Typen [Customer_Id]
DECLARE	@t AS Customer_Id;

-- Eintragen der gewünschten Kunden-Id
INSERT INTO @t(customer_id)
VALUES (1), (2), (3), (4), (5);

-- Ausführung der Prozedur
EXEC dbo.proc_CustomerList @t;
GO

Tabellenvariablen sind nur im RAM persistent?

Diese Aussage ist so nicht richtig. Selbstverständlich muss für die Erstellung einer Tabellenvariablen ebenfalls Speicher in TEMPDB allokiert werden. Hier spielen jedoch andere Faktoren eine Rolle, die zu der Annahme führen könnten, dass Tabellenvariablen nur im RAM vorhanden sind – die Art und Weise, wie Microsoft SQL Server (wie übrigens auch andere RDBMS-Systeme) Transaktionsdaten behandelt. Beim Schreiben von Datensätzen werden – entgegen landläufiger Meinung – die Daten nicht sofort in die Datenbank selbst geschrieben. Vielmehr werden lediglich Transaktionen unmittelbar in das Transaktionsprotokoll geschrieben; die Datenseiten selbst verbleiben zunächst im RAM. Durch einen CHECKPOINT werden die Daten selbst (dirty pages) erst in die Datenbankdatei(en) geschrieben. Mit dem nachfolgenden Skript wird eine Tabellenvariable erzeugt und 100 Datensätze in diese Tabellenvariable geschrieben. Anschließend werden die Daten mit ihrer physikalischen Position in der Datenbank ausgegeben. Nachdem ein CHECKPOINT ausgeführt wurde, werden die Daten unmittelbar in der Datenbankdatei untersucht!

SET NOCOUNT ON;
GO

-- Was steht derzeit im Transaktionsprotokoll?
SELECT * FROM sys.fn_dblog(NULL, NULL);
GO

-- Beginn der Transaktion
BEGIN TRANSACTION
DECLARE	@t TABLE
(
    Id  INT        NOT NULL  IDENTITY(1,1),
    C1  CHAR(1000) NOT NULL  DEFAULT ('a filler only!'),
    C2  DATE       NOT NULL  DEFAULT (GETDATE()),

    PRIMARY KEY CLUSTERED (Id)
);

-- Eintragen von 100 Datensätzen
DECLARE @i INT = 1
WHILE @i <= 100
BEGIN
    INSERT INTO @t DEFAULT VALUES;
    SET @i += 1;
END

-- Was wurde in das Transaktionsprotokoll geschrieben?
SELECT * FROM sys.fn_dblog(NULL, NULL);

-- Wo werden die Daten physikalisch gespeichert?
SELECT * FROM @t AS T
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
GO
COMMIT TRANSACTION;

-- Anzeigen der ersten Datenseite, 
-- auf der Daten der Tabellenvariable gespeichert sind.
DBCC TRACEON (3604);
DBCC PAGE ('tempdb', 1, 127, 1);
GO

Die nachfolgende Abbildung zeigt, dass die Daten der Tabellenvariable auf die Disk geschrieben wurden – sie sind also nicht nur im RAM präsent!

Warum Tabellenvariablen, obwohl es bereits temporäre Tabellen gibt?

Die nachfolgende – nicht abschließende – Liste gibt einen Überblick über die Besonderheiten von Tabellenvariablen.

• Tabellenvariablen haben wie lokale Variablen einen definierten Bereich, an dessen Ende sie automatisch gelöscht werden.
• Im Vergleich zu temporären Tabellen haben Tabellenvariablen weniger Neukompilierungen einer gespeicherten Prozedur zur Folge.
• Transaktionen, dauern nur so lange wie eine Aktualisierung der Tabellenvariable. Deshalb benötigen Tabellenvariablen weniger Ressourcen für Sperren und Protokollierung.
• Da Tabellenvariablen einen beschränkten Bereich haben und nicht Bestandteil der persistenten Datenbank sind, sind sie von Transaktionsrollbacks nicht betroffen.

Wann mit Tabellenvariablen oder temporärer Tabelle arbeiten?

Die Antwort auf diese Frage ist recht einfach: “It depends”. Es hängen viele Faktoren von der Entscheidung für die richtige Strategie ab:

• Anzahl der Zeilen, die in die Tabelle eingefügt werden.
• Anzahl der Neukompilierungen, aus denen die Abfrage gespeichert wird.
• Typ der Abfragen und deren Abhängigkeit von Indizes und Statistiken bei der Leistung.

In manchen Situationen ist es nützlich, eine gespeicherte Prozedur mit temporären Tabellen in kleinere gespeicherte Prozeduren aufzuteilen, damit die Neukompilierung für kleinere Einheiten stattfindet. Im Allgemeinen werden Tabellenvariablen verwendet, wenn es um ein kleines Datenvolumen geht! Wenn große Datenmengen wiederholt verwendet werden müssen, hat die Arbeit mit temporären Tabellen deutliche Vorteile. Außerdem können Indizes für die temporäre Tabelle erstellt werden, um die Abfragegeschwindigkeit zu erhöhen. Microsoft empfiehlt zu testen, ob Tabellenvariablen für eine bestimmte Abfrage oder gespeicherte Prozedur geeigneter sind als temporäre Tabellen. Eine Aufzählung von Vor- und Nachteilen temporärer Tabellen und Tabellenvariablen hat Yogesh Kamble in einem “Quiz” gegeben.

Herzlichen Dank für’s Lesen.

Weiterführende Weblinks

Datenbank TEMPDB	http://msdn.microsoft.com/de-de/library/ms190768.aspx
CREATE TABLE	http://msdn.microsoft.com/de-de/library/ms174979.aspx
EXECUTE AS	http://msdn.microsoft.com/de-de/library/ms188354.aspx
Tabellenvariablen	http://msdn.microsoft.com/de-de/library/ms175010.aspx