Lokale temporäre Tabellen werden mit CREATE erstellt und sind während der Session gültig in der sie erzeugt wurden. In dieser Zeit können sie von anderen Prozeduren verwendet werden. Nach Beendigung der Session werden sie automatisch gelöscht.

Globale temporäre Tabellen sind übergreifend für alle Sessions und Benutzer verfügbar und werden automatisch gelöscht sobald keine Session mehr die Tabelle referenziert.

Tabellenvariablen sind, wie der Name bereits andeutet, Variablen und werden mit dem DECLARE-Befehl erstellt. Wie alle Variablen existiert diese nur innerhalb des Ausführungsscopes (z.B: Stored Procedure, BEGIN/END-Block, etc.). Dies bedeutet, nach Verlassen des Scopes sind sämtliche in der Variablen gehaltenen Daten automatisch gelöscht. Entgegen der landläufigen Meinung, werden sowohl temporäre Tabellen als auch Tabellenvariablen in der TempDB gespeichert.

Hier die wesentlichen Unterschiede:

• Da temporäre Tabellen ganz normale Tabellen sind, können Indizes und Statistiken angelegt werden. Auf Tabellenvariablen dagegen können nur Clustered Indizes und keine Statistiken erstellt werden (für SQL Server 2014 ist die Möglichkeit von Non Clustered Indizes angekündigt).
• Tabellenvariablen nehmen nicht an Transaktionen teil. Sie erfordern deswegen weniger logging und locking und bringen weniger Overhead mit sich. Temporäre Tabellen dagegen nehmen (mit reduzierten Logging) an Transaktionen teil. Sie sind dadurch potentiell weniger perfomant aber profitieren dafür auch von deren Vorteilen.
• Auf temporären Tabellen könne sowohl DDL Operationen (Alter, Drop) als auch DML Operationen (SELECT, INSERT, UPDATE, DELETE) vorgenommen werden.
• Temporäre Tabellen führen oft dazu, das Stored Procedures neu kompiliert werden müssen. Tabellenvariablen haben dieses Problem nicht. (KB 243586)
• Es ist nicht möglich temporäre Tabellen in Functions zu verwenden. Tabellenvariablen hingegen können verwendet werden. Es ist auch möglich, Tabellenvariablen als Rückgabetyp einer Function zu definieren.
• Temporäre Tabellen können mit SELECT INTO erstellt werden, ohne vorher die Struktur definieren zu müssen. Tabellenvariablen müssen immer zuerst definiert werden.

Je nach Version des SQL Servers gibt es weitere Einschränkungen bei der Verwendung von Tabellenvariablen:

• SQL Server 2005
  Ab dieser Version ist es möglich Tabellenvariablen mit INSERT EXEC zu befüllen:
  

  INSERT @tabelle EXEC usp_beispiel

• SQL Server 2008
  
  Tabellenvariablen können nun Eingangs- oder Ausgangsparameter von Stored Procedures sein. Dazu müssen diese aber zuvor als Datentyp im SQL Server definiert sein.

  CREATE TYPE tt_beispiel AS TABLE (spid int, cmd varchar(50))
  GO
  
  CREATE PROCEDURE usp_beispiel @spids tt_beispiel READONLY AS SELECT * FROM @spids
  GO
  
  DECLARE @spids tt_beispiel INSERT INTO @spids 
  SELECT TOP 10 spid, cmd FROM sys.sysprocesses 
  
  EXEC usp_beispiel @spids=@spids

• SQL SERVER 2014
  
  In der neusten Version ist es möglich non clustered Indizes auf Tabellenvariablen zu definieren.

  DECLARE @Beispiel TABLE (
  
  Spalte1 INT INDEX IX_Spalte1 CLUSTERED,
  
  Spalte2 INT INDEX IX_Spalte2 NONCLUSTERED,
  
              INDEX IX_Composite NONCLUSTERED(Spalte1, Spalte2)
  
  );

Fazit

Wann sollte nun welche Variante verwendet werden? Die Antwort auf diese Frage lautet wie so oft "kommt drauf an…". Bei kleinen Datenmengen sind Tabellenvariablen durch den geringeren Overhead schneller und damit die erste Wahl. Für größere Datenmengen empfiehlt sich eine temporäre Tabelle, um dem Query Optimizer mit Statistiken zu helfen. In Functions und Stored Procedures sollten nach Möglichkeit Tabellenvariablen verwendet werden, außer es handelt sich um verschachtelte Stored Procedures welche Ergebnisse zwischenspeichern. Im Zweifel sollten Performance Tests durchgeführt werden, um die beste Methode zu ermitteln.