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.". Dann darf sie automatisch jeder lesen, schreiben, allerdings auch löschen. Sicherheitskritische Daten sollte man aber in so eine Tabelle ohnehin nicht schreiben.
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.
