Was sind temporäre Objekte?
Grundsätzlich ermöglicht euch der SQL Server das Anlegen temporärer Objekte, mit denen ihr arbeiten könnt. Das ist praktisch, da ihr nicht unbedingt jedes Zwischenergebnis oder jede Hilfstabelle in eurer Datenbank persistieren möchtet, wenn ihr mit größeren Datenmengen arbeitet. Temporär bedeutet dabei, dass die Tabellen in der tempdb gespeichert werden und automatisch gelöscht werden. Wann sie gelöscht werden, ist unterschiedlich, das möchten wir uns gleich noch genauer ansehen. Es gibt aber nicht nur die Möglichkeit, Tabellen temporär anzulegen, das funktioniert auch mit Prozeduren.
Wie könnt ihr mit temporären Objekten arbeiten?
Grundsätzlich gibt es zwei Arten von temporären Objekten im SQL Server: lokale und globale temporäre Objekte. Ihr könnt temporäre Objekte daran erkennen, dass ihre Namen mit einer Raute (#) beginnen. Ob ein Objekt lokal oder global ist, erkennt ihr daran, ob es eine oder zwei Rauten vorangestellt hat. Sehen wir uns einmal die Unterschiede zwischen beiden Objekten an. Dafür erzeugen wir zunächst eine lokale temporäre Tabelle:
DROP TABLE IF EXISTS #temporary
CREATE TABLE #temporary
(
ID int IDENTITY(1,1)
,Ninja varchar(150)
)
Nun können wir in diese Tabelle einfügen und abfragen.
INSERT INTO #temporary (Ninja)
VALUES ('Lushikute'), ('Chimozuki')
SELECT
ID
,Ninja
FROM #temporary
Wir können die Tabelle sogar indizieren:
CREATE CLUSTERED INDEX ix_ninjaids ON #temporary (ID)
Um den Unterschied zwischen lokalen und globalen temporären Objekten zu verstehen, öffnet ihr jetzt im Azure Data Studio oder im SQL Server Management Studio eine neue Abfrage auf die Datenbank. Wenn ihr im so entstandenen neuen Abfrage-Tab nun versucht, die temporäre Tabelle abzufragen, so bekommt ihr die Meldung, dass sie nicht existiert.
Tun wir nun also dasselbe mit einer globalen temporären Tabelle:
DROP TABLE IF EXISTS ##temporary
CREATE TABLE ##temporary
(
ID int IDENTITY(1,1)
,Ninja varchar(150)
)
INSERT INTO ##temporary (Ninja)
VALUES ('Lushikute'), ('Chimozuki')
SELECT
ID
,Ninja
FROM ##temporary
Wenig überraschend erhalten wir bis zu dieser Stelle dasselbe Resultat, wie als wir eine lokale temporäre Tabelle verwenden hatten. Fragt ihr nun im zweiten Tab die globale temporäre Tabelle ab, so erhaltet ihr Resultate:
SELECT
ID
,Ninja
FROM ##temporary
Ihr habt gesehen, auf ein lokales temporäres Objekt könnt ihr nur innerhalb der Session zugreifen, in der ihr es erzeugt hattet, auf ein globales temporäres Objekt aber auch in anderen Sessions.
Und wie lange “leben” die temporären Objekte? Das kommt darauf an. Ein lokales temporäres Objekt wird mit eurer Session beendet. Das bedeutet, wenn in eurem Programmcode, ETL-Paket, SSMS oder Data Studio die Verbindung geschlossen wird, in der das temporäre Objekt erzeugt wurde, dann ist dieses Objekt auch nicht mehr verfügbar. Anders ist das bei globalen Objekten: so lange eine Session existiert, die das Objekt nutzt, wird es am Leben gehalten, wenn die letzte Session beendet wird, in der das Objekt verwendet wurde, dann wird auch hier das Objekt gelöscht und ist unwiederbringlich verloren.
Nun haben wir die ganze Zeit mit temporären Tabellen hantiert, aber von Objekten gesprochen, legen wir also zum Schluss für diese Woche noch eine temporäre Prozedur an:
DROP PROCEDURE IF EXISTS #sp_temp
GO
CREATE PROCEDURE #sp_temp AS
SET NOCOUNT ON;
SELECT DB_NAME() AS ThisDB;
GO
Auch hier gilt, dass ihr die Prozedur innerhalb derselben Session wie jede andere Prozedur verwenden könnt:
EXEC #sp_temp
Aus einer anderen Session schlägt dieser Aufruf aber wieder fehlt, da das Objekt dem SQL Server nicht bekannt ist:
Could not find stored procedure '#sp_temp'.
Legt ihr die Prozedur aber als globales temporäres Objekt an, so wird auch sie in der zweiten Session gefunden:
DROP PROCEDURE IF EXISTS ##sp_temp
GO
CREATE PROCEDURE ##sp_temp AS
SET NOCOUNT ON;
SELECT DB_NAME() AS ThisDB;
GO
EXEC ##sp_temp
Diese könnt ihr nun auch in einer zweiten Session ausführen:
EXEC ##sp_temp
Temporäre Objekte können hilfreich sein, um Zwischenergebnisse bei größeren Prozessen zu speichern oder um Prozeduren einzusetzen, die ihr nicht außerhalb eines Prozesses weiterverwenden möchtet. Es gilt dabei natürlich den Scope des Objekts zu beachten und zu unterscheiden, ob das Objekt auch außerhalb der Session verfügbar ist.
Referenzen
- Dokumentation von Microsoft zu temporären Tabellen
- Dokumentation von Microsoft zu temporären Prozeduren
