Temporäre Tabellen gegen Tabellenvariablen – beide Ansätze im Vergleich:
(lokale/globale) temporäre Tabellen
Temporäre Tabellen sind reguläre Datenbankobjekte wie Standardtabellen. Sie werden nur in der TempDB verwaltet. Somit kann man sie beliebig indizieren.
Die Anlage, die Änderung und das Löschen einer temporärer Tabelle bedingen eine Schemaänderung und führen somit zu einer Rekompilierung.
Sie werden in der TempDB abgelegt.
- Lokale temporäre Tabellen sind nur für den Nutzer sichtbar, der sie angelegt hat. Sie werden spätestens automatisch gelöscht, wenn die aufrufende Verbindung geschlossen wird.
- Globale temporäre Tabellen sind für alle Benutzer nutzbar und werden spätestens mit dem Schließen der letzten Verbindung auf diese Tabelle (das muss nicht die initiale Verbindung sein!) gelöscht.
Temporäre Tabellen sind (mit eingeschränktem Logging!) Bestandteil einer Transaktion. Somit bieten sie Sicherheit zum Preis erhöhter Laufzeit.
Eine Verwendung von temporären Tabellen innerhalb von Funktionen ist nicht möglich. Der Einsatz in Prozeduren ist problemlos.
Ähnlich wie reguläre Tabellen verfügen auch temporäre Tabellen über die gesamte Bandbreite an Statistiken. Diese Eigenschaft ist besonders für die Indizierung relevant.
Man kann sowohl DDL- als auch DML-Operationen auf temporäre Tabellen anwenden! Eine Angabe von Fremdschlüsselbeziehungen zu/auf temporäre Tabellen ist nicht möglich.
Temporäre Tabellen werden genauso befüllt wie man es mit normalen Tabellen macht (INSERT, UPDATE, MERGE, …). Sie müssen nicht zwingend vorher definiert (CREATE) werden, sondern können die Struktur von anderen Objekten übernehmen (INSERT INTO #temp SELECT * FROM original).
Temporäre Tabellen kennen Sperren und verhalten sich im Umgang mit diesen genauso wie ihre herkömmlichen Verwandten. Datenbankentwickler können dieses Verhalten mit Query-Hints beeinflussen.
Die Verwendung von temporären Tabellen als Parameter ist nicht möglich. Man kann sie aber in JOINS als Datenbankobjekt nutzen.
Tabellenvariablen
Tabellenvariable sind nur eingeschränkt indizierbar. Clusterd Indizes (ohne Statistiken!) funktionieren in allen Versionen, mit SQL Server 2014 sind Non Clustered Indizes dazu gekommen.
Eine Rekompilierung ist bei Variablen nicht notwendig, da man Datenbankschemata nicht ändert.
Anders als oft behauptet, liegen Tabellenvariablen nicht nur im RAM – sie werden ebenso in der TempDB verwaltet.
Sie existieren nur innerhalb des Ausführungsscopes, in dem sie generiert wurden. Hierbei handelt es sich bspw. um ganze Prozeduren oder einzelne BEGIN-END-Blöcke. Wird der Scope verlassen, zerstören sich die Objekte mitsamt Inhalt selbst.
Tabellenvariable nehmen nicht an Transaktionen teil. Somit bieten sie stark verringerte Laufzeit (u.a. kein Logging), bieten aber keinerlei Schutz.
Tabellenvariablen kann man sowohl in Funktionen als auch in Prozeduren einsetzen.
Anders als reguläre Tabellen verfügen Tabellenvariable über keinerlei Statistiken. Dies erklärt die Schwierigkeiten des Optimizers, Abfragen auf Tabellenvariable zu beschleunigen (fehlende Indizes).
Es ist nicht möglich, die Struktur von bestehenden Tabellenvariablen zu ändern (bspw. per ALTER). Sie werden auch nicht wie bei DDL üblich per CREATE (DROP) erstellt oder gelöscht, sondern per DECLARE aufgebaut.
Die Wert(e)zuweisung geschieht nicht per SET sondern bspw. über ein INSERT @temp EXEC proc.
Tabellenvariablen kennen ein Sperrverhalten. Aufgrund des eingeschränkten Gültigkeitsbereiches sind konkurrierende Zugriffe ohnehin ausgeschlossen.
Die Nutzung von Tabellenvariablen ist in Stored Procedures sowohl als Eingangs- wie auch als Ausgangsparameter möglich. Als Beispiel sei hier das Projekt „BattleShips“ genannt…
Die Übersicht stellt die Vor- und Nachteile beider Lösungsansätze gegenüber. Es lohnt sich beide Varianten gegen eine entsprechend große Datenmenge laufen zu lassen. Schnell wird klar, dass temporäre Tabellen nicht ganz so flexibel sind. Sie spielen ihre Stärke aufgrund der Indizierung aber bei größeren Datenmengen aus und bringen eine spürbare Laufzeitoptimierung.
