Sonntag, 18. November 2012

Temporäre Tabellen vs. Tabellenvariablen (Funktionalität und Gültigkeit)

Mit diesem Beitrag möchte ich mich intensiv mit einem Thema beschäftigen, das in der Community teilweise sehr heftig diskutiert wird. Seit der Version Microsoft SQL Server 2000 gibt es die Möglichkeit, Tabellenvariablen zu benutzen. Seit es Tabellenvariablen gibt, werden die Vor- und Nachteile aufs heftigste diskutiert und um die Pros und Contras ranken sich viele Mythen. Einige von diesen “Mythen” möchte ich – durch Beispiele untermauert – widerlegen.

Temporäre Relationen

Temporäre Tabellen sind, wie der Name es bereits sagt, “temporär”. Temporäre Tabellen unterscheiden sich technisch nicht durch normale Relationen, die sich in den Datenbanken befinden. Das bedeutet, dass temporäre Relationen erstellt werden können, gelöscht werden können und Daten können in temporären Relationen beliebig hinzugefügt, geändert oder gelöscht werden. Temporäre Relationen verhalten sich absolut identisch zu Relationen in der Datenbank.

Der Unterschied zu “normalen” Relationen besteht in zwei wesentlichen Punkten, die nachfolgend etwas näher beleuchtet werden sollen.

Temporäre Relationen 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 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 Relationen beginnen IMMER mit “#” (lokal) oder “##” (global)
  • Temporäre Relationen werden automatisch gelöscht, sobald sie nicht mehr verwendet werden
  • Globale temporäre Relationen sind für ALLE Datenbanksitzungen verfügbar
  • Lokale temporäre Relationen

    Lokale temporäre Relationen sind nur innerhalb der Datenbanksitzung gültig, in der diese Relation erstellt wurde. Lokale temporäre Tabellen beginnen immer mit einem “#”. Wird die Datenbankverbindung getrennt, werden lokale temporäre Objekte, die während dieser Datenbanksitzung erstellt wurden, automatisch gelöscht. Das nachfolgende Script verdeutlicht den Sachverhalt etwas genauer. Dazu führen Sie bitte das vollständige Script in einem Abfragefenster von SQL Server Management Studio aus.

    -- Gültigkeitsbereich von temporären Tabellen
    RAISERROR ('Erstellen eines SQL Benutzers Temp_User1', 0, 1)
    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Temp_User1')
        CREATE LOGIN Temp_User1 WITH PASSWORD = 'abcdef', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
        GO

    RAISERROR ('Erstellen eines SQL Benutzers Temp_User1', 0, 1)
    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Temp_User1')
        CREATE LOGIN Temp_User1 WITH PASSWORD = 'abcdef', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
        GO

    RAISERROR ('Der Benutzer Temp_User1 erstellt nun eine temporäre Tabelle', 0, 1)
    EXECUTE AS LOGIN = 'Temp_User1'
        SET NOCOUNT ON
        GO

        IF OBJECT_ID('tempdb..#Temp_User1_Table', 'U') IS NOT NULL
            DROP TABLE #temp_User1_Table
            GO

        CREATE TABLE #Temp_User1_Table
        (
            Id      int             NOT NULL    IDENTITY,
            spid    int             NOT NULL    DEFAULT (@@spid),
            myText  varchar(100)    NOT NULL    DEFAULT (suser_sname())
        )

        GO

        INSERT INTO #Temp_User1_Table DEFAULT VALUES
        GO 100

        SELECT * FROM #Temp_User1_Table
    REVERT
    GO

    RAISERROR ('Nun soll Benutzer Temp_User2 auf die von Temp_User1 erstellte Tabelle zugreifen', 0, 1)
    EXECUTE AS LOGIN = 'Temp_User2'
        SELECT * FROM #Temp_User1_Table;
    REVERT
    GO

    Wird der obige Code komplett in einem Abfragefenster des SQL Server Management Studios ausgeführt, so kann man erkennen, dass der Benutzer [temp_User2]  ebenfalls auf die von Benutzer [temp_User1] erstellte Tabelle zugreifen kann. Das hängt damit zusammen, dass das gesamte Script in einer einzigen Datenbanksitzung ausgeführt wird.

    Wird jedoch der folgende Code in einem NEUEN Abfragefenster ausgeführt, erhält man die eine entsprechende Fehlermeldung, dass die gewünschte Tabelle nicht vorhanden ist.

    RAISERROR ('Nun soll Benutzer Temp_User2 auf die von Temp_User1 erstellte Tabelle zugreifen', 0, 1)
        EXECUTE AS LOGIN = 'Temp_User2'
        SELECT * FROM #Temp_User1_Table;
    REVERT
    GO

    Ergebnis:

    Nun soll Benutzer Temp_User2 auf die von Temp_User1 erstellte Tabelle zugreifen
    Msg 208, Level 16, State 0, Line 4
    Ungültiger Objektname '#Temp_User1_Table'.

    Die Fehlermeldung ist eindeutig und besagt, dass in der neu erstellten Datenbanksitzung ein Objekt mit dem Namen [#Temp_User1_Table] nicht existiert.

    Globale temporäre Relationen

    Globale temporäre Relationen weisen im Unterschied zu lokalen temporären Relationen als erste Zeichen ihres Namens zwei Nummernzeichen (##) auf. Nachdem sie erstellt wurden, sind sie für jeden Benutzer sichtbar. Sie werden gelöscht, nachdem alle Benutzer, die auf diese Tabelle verweisen, die Verbindung mit der Instanz von SQL Server getrennt haben. Nehmen wir erneut das obige Script und ändern es geringfügig, indem das zu erstellende Objekt nicht [#Temp_User1_Table] heißt sondern [##Temp_User1_Table]. Dadurch wird das Objekt zu einem globale temporären Tabellenobjekt.

    -- Gültigkeitsbereich von temporären Tabellen
    RAISERROR ('Erstellen eines SQL Benutzers Temp_User1', 0, 1)
        IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Temp_User1')
        CREATE LOGIN Temp_User1 WITH PASSWORD = 'abcdef', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
    GO

    RAISERROR ('Erstellen eines SQL Benutzers Temp_User1', 0, 1)
        IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Temp_User1')
        CREATE LOGIN Temp_User1 WITH PASSWORD = 'abcdef', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
    GO

    RAISERROR ('Der Benutzer Temp_User1 erstellt nun eine temporäre Tabelle', 0, 1)
    EXECUTE AS LOGIN = 'Temp_User1'
        SET NOCOUNT ON
        GO

        IF OBJECT_ID('tempdb..##Temp_User1_Table', 'U') IS NOT NULL
        DROP TABLE ##temp_User1_Table
        GO

        CREATE TABLE ##Temp_User1_Table
        (
            Id     int          NOT NULL IDENTITY,
            spid   int          NOT NULL DEFAULT (@@spid),
            myText varchar(100) NOT NULL DEFAULT (suser_sname())
        )

        GO

        INSERT INTO ##Temp_User1_Table DEFAULT VALUES
        GO 100

        SELECT * FROM ##Temp_User1_Table
        REVERT
    GO

    Nachdem die Relation erstellt wurde, wird in einem zweiten Abfragefenster von SQL Server Management Studio erneut der Versuch gestartet, als Benutzer [temp_User2] auf die von Benutzer [temp_user1]  erstellte Relation zuzugreifen. Der Zugriff ist nun möglich und die Daten werden angezeigt. Dieses Verhalten zeigt aber auch einen extremen Schwachpunkt am Konzept der temporären Relationen:

    Wenn eine globale temporäre Tabelle erstellt wird, können ALLE Datenbankbenutzer uneingeschränkt auf das Objekt zugreifen. Es gibt KEINE Möglichkeit, diese Berechtigungen mittels DENY oder REVOKE einzuschränken. Deswegen sollte gründlich überlegt werden, wann man globale Tabellen einsetzt. Bei hochsensiblen Daten sollte möglichst auf die Verwendung von globalen temporären Relationen verzichtet werden!

    Temporäre Tabellen können indiziert werden

    Da sich temporäre Relationen wie normale Datenbankrelationen verhalten, unterliegen die temporären Relationen in Bezug auf die Indexierung keinerlei Einschränkungen. Sie können genau so indiziert werden, wie andere Relationen.

    IF OBJECT_ID('tempdb..#master_table', 'U') IS NOT NULL
        DROP TABLE #master_table
        GO

    CREATE TABLE #master_table
    (
        Id          int         NOT NULL    IDENTITY    PRIMARY KEY CLUSTERED,
        Customer    char(89)    NOT NULL    DEFAULT ('a new customer')
    )
    GO

    CREATE INDEX ix_master_table ON #master_table (Customer);
    GO

    Fremdschlüsselbeziehungen zwischen temporären Relationen sind nicht möglich

    Zwar können Indexe erstellt werden aber eine wesentliche Einschränkungen von temporären Objekten zu “normalen” Relationen gibt es dann doch noch – temporäre Tabellen können nicht für DRI (deklarative referenzielle Integrität) verwendet werden. Das nachfolgende Script wird ausgeführt liefert aber bei der Erstellung des Fremdschlüssels einen Fehler.

    IF OBJECT_ID('tempdb..#detail_table', 'U') IS NOT NULL
        DROP TABLE
    #detail_table
        GO

    IF OBJECT_ID('tempdb..#master_table', 'U') IS NOT NULL
        DROP TABLE
    #master_table
        GO

    CREATE TABLE #master_table
    (
        Id          int         NOT NULL    IDENTITY    PRIMARY KEY CLUSTERED,
        Customer    char(89)    NOT NULL    DEFAULT ('a new customer')
    )
    GO

    CREATE TABLE #detail_table
    (
        Order_Id    int         NOT NULL    IDENTITY,
        Customer_Id int         NOT NULL    REFERENCES #master_table (Id),
        Order_NO    char(10)    NOT NULL,

        CONSTRAINT pk_detail_table PRIMARY KEY CLUSTERED
        (
            Order_id,
            Customer_id
        )
    )
    GO

    Sobald das Script ausgeführt wird, wird anschließend die folgende Informationsmeldung ausgegeben:

    Die Definition der FOREIGN KEY-Einschränkung '#detail_table' wird für die temporäre Tabelle ausgelassen. FOREIGN KEY-Einschränkungen werden für lokale und globale temporäre Tabellen nicht erzwungen.

    Diese Einschränkung erscheint logisch, da SQL Server automatisch temporäre Objekte wieder löscht, sobald die Datenbanksitzung geschlossen wird (lokale Relationen) oder aber keine weiteren Datenbanksitzungen mehr auf die Objekte zugreifen (globale Objekte). Dieser Mechanismus würde unterbrochen werden, wenn z. B. die globale Relation ##master_table nicht mehr im Zugriff wäre, während noch Zugriffe auf ##detail_table bestünden. SQL Server könnte dann auf Grund der FOREIGN KEY Einschränkungen das Objekt nicht löschen.

    Tabellenvariablen

    Tabellenvariablen wurden mit Microsoft SQL Server 2000 erstmals eingeführt. Tabellenvariablen unterscheiden sich in sehr vielen Dingen von temporären Relationen obwohl sie – scheinbar – nichts anderes sind. Der Vorteil von Tabellenvariablen liegt darin, dass sie wie Variablen deklariert und verwendet werden.

    Grundsätzlich besteht aber ein besonders signifikanter Unterschied in der Bereitstellung des Objekts (Scope). Wie bereits erwähnt, sind temporäre Relationen gültig in einer Datenbanksitzung (lokal) oder für alle Benutzer (global). Eine Tabellenvariable ist immer nur innerhalb des Batches gültig. Das nachfolgende Beispiel verdeutlicht den Zusammenhang.

    SET NOCOUNT ON;
    GO

    DECLARE @t TABLE
    (
        Id      int             NOT NULL    IDENTITY PRIMARY KEY CLUSTERED,
        spid    int             NOT NULL    DEFAULT (@@spid),
        mytext  varchar(100)    NOT NULL    DEFAULT ('just a comment')
    );


    -- Eintrage von Werten und Überprüfung
    INSERT INTO @t DEFAULT VALUES;
    SELECT
    * FROM @t;
    GO

    -- Fehler, da ein GO den vorherigen Batch abschließt
    SELECT * FROM @t;
    SET NOCOUNT OFF;
    GO

    Zunächst wird eine Tabellenvariable deklariert und anschließend ein Datensatz eingetragen. Während das erste SELECT-Statement korrekt ausgeführt wird, wird beim zweiten Mal eine Fehlermeldung ausgegeben:

    Msg 1087, Level 15, State 2, Line 3
    Die @t-Tabellenvariable muss deklariert werden.

    Die Aussage, dass ein Fehler in Line 3 aufgetreten ist, stimmt insoweit, als dass nach dem GO die nächste Anweisung drei Zeilen weiter ausgeführt wird. Hierbei handelt es sich um das zweite SELECT. Der Geltungsbereich einer Tabellenvariablen ist also immer nur innerhalb eines Batches gültig.

    Eine weitere Einschränkung für Tabellenvariablen besteht in der Definition seiner Struktur. Eine temporäre Relation verhält sich – wie oben dargelegt – wie eine normale Relation bis auf die Einschränkung, dass keine FOREIGN KEYS definiert werden können. Die Einschränkungen für Tabellenvariablen gehen aber noch wesentlich weiter, wie das nachfolgende Beispiel zeigen soll.

    DECLARE @t TABLE
    (
        Id      int             NOT NULL    IDENTITY PRIMARY KEY CLUSTERED,
        spid    int             NOT NULL    DEFAULT (@@spid),
        mytext  varchar(100)    NOT NULL    DEFAULT ('just a comment')
    );

    -- Erstellung eines zusätzlichen Index
    CREATE INDEX
    ix_tbl_t ON @t (spid);

    Diese Anweisung wird mit Fehler 102 fehlschlagen. Ursache hierfür ist die Limitierung von Tabellenvariablen. Es können außerhalb der Strukturdefinition (DECLARE) keine weiteren DDL-Anweisungen mehr verwendet werden, die sich auf die Tabellenvariable beziehen. Auch bei der Definition von Indexen gibt es Grenzen, die beachtet werden müssen. Das nachfolgende SQL-Statement schlägt fehl, da mittels eines CONSTRAINTS versucht wurde, einen “clustered index” zu definieren.

    DECLARE @t TABLE
    (
        Record_Id      int           NOT NULL,
        Property_Id    int           NOT NULL,
        PropertyValue  varchar(max)  NOT NULL,

        CONSTRAINT pk_t_cluster PRIMARY KEY CLUSTERED
        (
            Record_Id,
            Property_Id
        )
    );

    Somit sollte schnell erkennbar sein, wo die Grenzen in Bezug auf Indexe liegen. Es können nur Einschränkungen definiert werden, die sich unmittelbar auf das Attribut auswirken (CLUSTERED, UNIQUE). Indexe, die sich aus mehreren Attributen zusammensetzten, sind auf Grund dieser Beschränkungen nicht möglich. Zusätzliche Indexdefinitionen können für Tabellenvariablen auf Grund ihrer Einschränkungen nicht definiert werden.

    Warum Tabellenvariablen, obwohl es bereits temporäre Tabellen gibt

    • 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 wird mit einer Tabellenvariablen gearbeitet und wann wird mit temporären Tabellen gearbeitet?

    Die Antwort auf diese Frage ist recht schwierig zu geben. Es hängen mehrere Faktoren von der Entscheidung 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 nicht so große Datenvolumen geht oder die Daten wiederholt verwendet werden müssen. In diesem Fall hat die Arbeit mit temporären Tabellen deutliche Vorteile. Außerdem können Indizes für die temporäre Tabelle erstellt werden, um die Abfrageleistung zu erhöhen. Jedoch kann jedes Szenario anders aussehen. Microsoft empfiehlt zu testen, ob Tabellenvariablen für eine bestimmte Abfrage oder gespeicherte Prozedur geeigneter sind als temporäre Tabellen.

    Eine vollständige Liste aller Vor- und Nachteile von temporären Tabellen vs. Tabellenvariablen hat Yogesh Kamble in einem “Quiz” von Pinal Dave auf SQLAuthority.com gegeben. Von den vielen Unterschieden werde ich einige wesentliche in den nächsten Beiträgen genauer beschreiben und durch Beispiele die Vor- und Nachteile der Unterschiede erläutern.

    Herzlichen Dank für’s Lesen.

    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
    SQL Authority http://blog.sqlauthority.com/

    Keine Kommentare :

    Kommentar veröffentlichen