Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Wie temporäre Tabellen auf Existenz prüfen?

30. März 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Temporäre Tabellen werden im MS SQL Server gerne verwendet, um kurzzeitig Zwischenergebnisse abzuspeichern und mit denen dann bequem mengenorientiert weiter arbeiten zu können.
Man unterscheidet bei den temporären Tabellen solche mit einem lokalen Gültigkeitsbereich (beginnen mit #), die man also nur in der eigenen, aktuellen Session verwenden kann und solche, die global, also auch in anderen Session verfügbar sind (beginnen mit ##). Ein Vorteil ist auch, das mit Beenden der Session (bei lokal temporäre) bzw. wenn keine Session mehr die (globale) temporäre Tabelle verwendet, diese automatisch wieder gelöscht wird.

Zur Fehlervermeidung prüft man vor Anlage einer (temporären) Tabelle, ob schon eine mit solchem Namen existiert. Es könnte ja sein, das in einem Script zur vor in der Session schon eine angelegt und vergessen wurde, diese explizit wieder zu Löschen; dann kommt es zum Laufzeitfehler.

Nur beim Überprüfen, ob eine solche existiert, ist etwas Wichtiges zu beachten. Legen wir zunächst mal 2 Tabellen an und überprüfen dann auch mittels ein T-SQL Script auf dem einfachen Weg:

 
USE
[AdventureWorks]
 GO 
 -- Temporäre Tabelle Lokal+Global anlegen 
 CREATE TABLE #Local ([ID] int); 
 CREATE TABLE ##Global([ID] int); 
 GO 
 -- Liefert kein Ergebnis 
 SELECT OBJECT_ID('#Local') AS LocID, 
  OBJECT_ID('##Global') AS GlobID; 
 -- Oder selektierbar? 
 SELECT name, object_id FROM sys.tables 
 WHERE name IN ('#Local', '##Global'); 


Die Abfrage der Object_ID() liefert NULL, die Selektion auf die Tabellen liefert keinen Datensatz, obwohl es die Tabellen ja nun gibt.

Kurz nachgedacht ist es klar, temporäre Objekte werden nicht in der aktuell verwendeten Datenbank angelegt, sondern immer in der Systemdatenbank „tempdb“. Also müssen wir mal da nachsehen.

 
-- Das liefert die ID-Abfrage:
 SELECT OBJECT_ID('tempdb..#Local') AS LocID, 
  OBJECT_ID('tempdb..##Global') AS GlobID; 


Das liefert uns nun eine Object_ID(), natürlich bezogen auf die tempdb. Kann man dort nun auch die Tabellen über sys.tables oder sys.objects selektieren? Fast. Um es kurz zu machen, selektiere ich gleich mit LIKE.

 
-- Auch selektierbar?
 SELECT name FROM tempdb.sys.tables 
 WHERE name LIKE '#Local%' 
  OR name like '##Global%' 


Das Ergebnis sieht dann als Beispiel so aus:
  

 name 
------------------------------------------------------------------- #Local_________________________________________________000000000023
 ##Global 


(Der #Local Eintrag ist gekürzt, insgesamt sind es eigentlich 128 Zeichen).

Unerwartetes Ergebnis?
Eigentlich ist es klar, eine lokal temporäre Tabelle ist nur in einer Session gültig und vom Namen her auch nur in der Session eindeutig. Andere Sessions können ihrerseits ebenfalls lokale Tabellen mit dem Namen anlegen und die müssen verwaltet werden können.
Der HEX-Wert als Suffix ist unabhängig von der Session, es ist eine fortlaufende Identity, die erst mit dem SQL Server Neustart wieder zurückgesetzt wird. Der ##Global Eintrag hat kein Suffix, da aus allen Sessions darauf zugegriffen werden kann und somit der Name auch für alle unique sein muss.

 

Fazit:

Die einzig funktionierende Möglichkeit auf die Existenz zu prüfen und wenn vorhanden, die temporäre Tabelle zu löschen ist:

 
IF
NOT OBJECT_ID('tempdb..#Local') IS NULL
  DROP TABLE #Local; 
 IF NOT OBJECT_ID('tempdb..##Global') IS NULL 
  DROP TABLE ##Global;
GO

Diesen Post teilen

Repost 0

Kommentiere diesen Post