Am Microsoft SQL Server gibt es drei offizielle Arten von temporären Tabellen, aber tatsächlich sind es sogar fünf. Je nach Art der Verwendung findet sich leicht etwas passendes.
sitzungsbezogene temporäre Tabellen
Die lokale Tabelle wird direkt in einer Session angelegt. Beispiel:
create table #localtable(
id integer identity(1,1) not null primary key,
f nvarchar(100) collate Latin1_General_CI_AS not null
);
- Die Tabelle existiert in der gesamten Verbindung ("Session") und damit in allen Prozeduren, die innerhalb aufgerufen werden.
Erst nach dem Ende der Verbindung wird die Tabelle automatisch entfernt. Es sei denn sie wird vorher explizit mit einem DROP-Befehl entfernt. - Andere Verbindungen können nicht auf die Tabelle zugreifen. Sie existiert nur "lokal" in dieser Session.
- Wird in einer Stored-Procedure (SP) eine gleichnamige Tabelle angelegt, dann wird dort eine eigene, unabhängige, temporäre Tabelle mit gleichem Namen angelegt. In diesem Fall kann man nur auf die "innere" zugreifen. (siehe nächster Typ)
- Es ist möglich einen Index auf die Tabele zu legen und/oder nachträglich weitere Constraints anzulegen.
lokale temporäre Tabellen
Die lokale Tabelle wird in einer Stored-Procedure (oder einem anderen Gültigkeitsbereich) angelegt. Die Syntax ist genau wie beim obigen Beispiel.
- Die Tabelle existiert aber nur in dieser Stored-Procedure (nennen wir sie mal SP1) und allen Prozeduren, die innerhalb von SP1 aufgerufen werden.
Nach dem Ende der Prozedur wird die Tabelle automatisch entfernt. Es sei denn sie wird vorher explizit entfernt. - Andere Prozeduren oder Verbindungen können nicht auf die Tabelle zugreifen. Sie existiert nur "lokal" in dieser Instanz der SP.
- Wird die Prozedur in anderen Sitzungen erneut aufgerufen, werden dort jeweils eigene, unabhängige temporäre Tabellen angelegt.
- Es ist möglich einen Index auf die Tabele zu legen und/oder nachträglich weitere Constraints anzulegen.
globale temporäre Tabellen
Bei der globalen Tabelle ist es egal in welchem Gültigkeitsbereich sie angelegt, wird. Sie kann in allen Stored-Procedures und Funktions zugegriffen werden. Für die Syntax anbei noch ein Beispiel:
create table ##globaltable(
id integer identity(1,1) not null primary key,
f nvarchar(100) collate Latin1_General_CI_AS not null
);
- Die Tabelle ist von jeder Verbindung aus zugreifbar, kann also von allen Stored-Procedures, Funktionen verwendet werden.
- Wird von einer anderen Verbindung (egal mit welchem Benutzer) versucht eine gleichnamibe Tabelle anzulegen, kommt die Fehlermeldung, dass es die Tabelle schon gibt.
- Nach dem Ende der ursprünglichen Verbindung wird die Tabelle automatisch entfernt, sofern keine andere Verbindung eine Sperre auf einen Datensatz, eine Seite oder die ganze Tabelle hält. Natürlich kann sie auch vorher explizit entfernt werden.
- Sobald die anderen Verbindungen auf die so eben entfernte Tabelle zuzugreifen, kommt die Meldung, dass sie nicht existiert.
- Es ist möglich einen Index auf die Tabele zu legen und/oder nachträglich weitere Constraints anzulegen.
In der Praxis finde ich diese Art der Tabellen sehr unpraktisch und würde von deren Verwendung abraten. Was hat man davon, wenn die Existenz der Tabelle so stark mit der anlegenden Verbindung zusammenhängt, dann wäre doch die sitzungsbezogene temporäre Tabelle deutlich verständlicher.
(normale) Tabelle in TempDB
Bei der normalen Tabelle in der TempDB muss man das Recht haben Tabellen anzulegen, also z.B. SysAdmin oder DdlAdmin in der TempDB sein. Für die Syntax wieder ein Beispiel:
create table tempdb.dbo.mytable(
id integer identity(1,1) not null primary key,
f nvarchar(100) collate Latin1_General_CI_AS not null
);
- Die Tabelle ist von jeder Verbindung aus zugreifbar, kann also von allen Stored-Procedures, Funktionen verwendet werden.
- Die Tabelle wird erst beim nächsten Start des SQL-Servers entfernt, sofern sie nicht vorher explizit entfernt wurde.
- Es ist möglich einen Index auf die Tabele zu legen und/oder nachträglich weitere Constraints anzulegen.
Rechte in der Praxis
1. Möglichkeit: Man kann die Benutzer, die diese Tabellen anlegen dürfen als User in der TempDB anlegen. Dass muss man allerdings nach jedem Server-Start machen, weil da die TempDB gelöscht und neu aus einer Kopie der Model-Datenbank angelegt wird. Diese Benutzer bekommen dann das Recht "CREATE TABLE". Dazu könnte man sich eine Autostart-Prozedur anlegen, die die anzulegenden User aus einer eigens dafür anzulegenden Tabelle liest und anlegt.
Nach dem Anlegen der Tabelle muss dann der Benutzer die Rechte auf die Tabelle explizit vergeben. Das alles ist umständlich und erfordert jede Menge Aufwand. Dafür ist es "nach Vorschrift": Jeder hat nur so viele Rechte, wie er benötigt.
2. Möglichkeit: Man gibt der Gruppe Public in der TempDB das Recht "CREATE TABLE". Auch hier schlage ich eine Autostart-Prozedur vor. Die anzulegenden Tabellen nennt man "tempdb.guest.
Tabellenvariablen
Und dann gibt es da noch die Tabellenvariablen. Sie funktionieren im Grunde fast genau wie lokale temporäre Tabellen
DECLARE @MyTempTable table (
id integer identity(1,1) not null primary key,
f nvarchar(100) collate Latin1_General_CI_AS not null);
- Die Tabellenvariable existiert aber nur in dieser Stored-Procedure (SP), aber nicht in den Prozeduren, die aufgerufen werden.
- Andere Prozeduren oder Verbindungen können nicht auf die Tabellenvariable zugreifen. Sie existiert nur "lokal" in dieser Instanz der SP.
- Wird die Prozedur in anderen Sitzungen erneut aufgerufen, werden dort jeweils eigene, unabhängige temporäre Tabellenvariablen angelegt.
- Es ist nicht möglich einen Index auf die Tabele zu legen oder nachträglich weitere Constraints anzulegen.
Collate in der Praxis
In obigen Beispielen habe ich immer die Collation angegeben. Das ist immer dann wichtig, wenn ich nicht sicher davon ausgehen kann, dass sie Server-Default-Collation mit der Collation in "meiner" Datenbank übereinstimmt. Da temporäre Tabellen in der TempDb angelegt werden, wird für Zeichenketten automatisch die Default-Collation des Servers verwendet, wenn es nicht anders angegeben wird.
Hallo Thomas,
ich habe folgendes Problem bei temporären Tabellen. Ich möchte eine Tabelle erstellen
was auch ohne Probleme funktioniert, nur sollte ich zuvor prüfen ob diese nicht schon
existiert. Genau da liegt mein Problem. Wie kann ich prüfen ob eine temporäre Tabelle
bereits erstellt wurde.
Ich Danke Dir schon mal im Vorraus
Viele Grüße Patrice
Hallo Patrice,
das geht mit einem Trick, der selten irgendwo dokumentiert ist. Wenn die temporäre Tabelle "#localtable" heißt, dann geht es so:
IF OBJECT_ID(N'tempdb..#localtable') IS NOT NULL-- Für Tabelle kann eine Object-Id ermittelt werden (ist nicht NULL).
-- Dann gibt es die Tabelle schon
DROP TABLE #localtable;
CREATE TABLE #localtable(
id integer identity(1,1) NOT NULL PRIMARY KEY,
f nvarchar(100) collate Latin1_General_CI_AS NOT NULL);
Oder so:
IF OBJECT_ID(N'tempdb..#localtable') IS NULL-- Für Tabelle kann keine Object-Id ermittelt werden.
-- Dann gibt es die Tabelle noch nicht.
CREATE TABLE #localtable(
id integer identity(1,1) NOT NULL PRIMARY KEY,
f nvarchar(100) collate Latin1_General_CI_AS NOT NULL);
Wichtig ist dabei in der Funktion OBJECT_ID vor den Namen der temp. Tabelle unbedingt "tempdb.." zu schreiben, man darf also kein Schema/Owner angeben. Das ist ein Hack in der Funktion, die dann nach temporären Tabellen in der aktuellen Session sucht.
Das funktioniert auch mit globalen temp. Tabellen ("##").
Habe es gerade in meinem Skript getestet mit
globalen temp. Tabellen und nun läuft alles
ohne Probleme.
Danke für die schnelle Antwort.
Viele Grüße Patrice
Vielen Dank für die #/##-Infos und die Existenzprüfung Object_id('tempdb..#…')!
Ist bereits im Einsatz.