dtcSoftware - powerShellDba

📌 Zwei mächtige Konzepte – kombiniert mit Bedacht

ÜBERBLICK

Dynamisches SQL und globale temporäre Tabellen (erkennbar am doppelten Rautezeichen ##) sind zwei Techniken, die in SQL Server oft im selben Atemzug genannt werden. Dynamisches SQL erlaubt es, Abfragen zur Laufzeit zu erzeugen – ideal für flexible Suchabfragen oder dynamische Pivots. Globale temporäre Tabellen existieren über Sessions hinaus, solange irgendeine Verbindung auf sie zugreift. Ihre Kombination kann extrem leistungsfähig sein, birgt aber auch Risiken wie SQL-Injection, Spaghetti-Code, Kollisionen mit anderen Prozessen oder nicht freigegebenen Speicher. Dieser Artikel zeigt, wie man beides sauber und sicher einsetzt.

💡 Wann ist die Kombination sinnvoll? Typischerweise, wenn Sie das Ergebnis einer dynamischen Abfrage in einer temporären Struktur ablegen müssen, die auch von anderen Sessions genutzt werden kann. Etwa bei ETL‑Prozessen, die Metadaten auslesen und dann in mehreren Schritten verarbeiten.

🔨 Dynamisches SQL – Flexibel, aber gefährlich

DYNAMIC SQL

Dynamisches SQL bedeutet, dass Sie einen String mit T‑SQL-Code erzeugen und diesen mit EXEC oder sp_executesql ausführen. Die Vorteile liegen auf der Hand:

Tabellen- oder Spaltennamen können zur Laufzeit bestimmt werden.
Komplexe, variable WHERE‑Klauseln lassen sich einfach aufbauen.
Pivot‑Abfragen mit unbekannter Anzahl von Spalten sind erst dadurch möglich.

Die Nachteile sind jedoch gewichtig: SQL‑Injection (wenn Benutzereingaben ungeprüft eingefügt werden), erschwerte Lesbarkeit und Wartbarkeit, sowie Plan‑Cache‑Probleme.

-- Einfaches dynamisches SQL (unsicher!)
DECLARE @TableName NVARCHAR(255) = 'Kunden';
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @TableName;
EXEC (@sql);

-- Sicherer mit QUOTENAME und sp_executesql
DECLARE @sql2 NVARCHAR(MAX) = 'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sys.sp_executesql @sql2;

⚠️ Goldene Regel: Verwenden Sie sp_executesql mit Parametern, wenn Werte übergeben werden müssen. Für Objektnamen (Tabellen, Spalten) verwenden Sie QUOTENAME().

🌐 Globale temp-Tabellen (##Tabelle) – Alle sehen sie

##TEMP

Normale temporäre Tabellen (#tabelle) existieren nur innerhalb einer Session (und deren verschachtelten Aufrufen). Globale temporäre Tabellen (##tabelle) hingegen sind für alle Sessions sichtbar und bleiben bestehen, bis die letzte Session, die sie verwendet, getrennt wird oder der SQL Server neu startet.

-- Session A erstellt eine globale Temp-Tabelle
CREATE TABLE ##GlobaleDaten (
  Id INT PRIMARY KEY,
  Wert NVARCHAR(100)
);
INSERT INTO ##GlobaleDaten VALUES (1, N'Test');

-- Session B kann sofort darauf zugreifen
SELECT * FROM ##GlobaleDaten;

-- Beenden der letzten Session löscht die Tabelle automatisch
DROP TABLE ##GlobaleDaten; -- optional; sonst automatisch

💡 Wichtige Einschränkung: Globale temporäre Tabellen sind kein Ersatz für dauerhafte Tabellen. Sie sind instabil – bei einem unerwarteten Verbindungsabbruch kann die Tabelle verschwinden. Zudem können Namenskollisionen auftreten, wenn zwei Prozesse dieselbe globale Tabelle erzeugen.

🔗 Kombination: Dynamisch eine ##Tabelle erstellen

PRAXIS

Stellen Sie sich vor, Sie müssen eine Tabelle mit einem variablen Namen erzeugen, die dann von mehreren Prozessen genutzt werden soll. Der naheliegende Weg: eine globale temporäre Tabelle mit dynamischem SQL anlegen. Allerdings ist Vorsicht geboten:

Der Name darf nicht fest sein, weil jede Ausführung eine eigene Tabelle benötigt (z. B. ##Export_123).
Nach der Erstellung muss man sicherstellen, dass die Tabelle auch wieder gelöscht wird.
Es kann zu Konflikten kommen, wenn zwei Aufrufe denselben Tabellennamen wählen.

-- Dynamische Erzeugung einer globalen temp-Tabelle mit eindeutigem Namen
DECLARE @BatchId INT = NEXT VALUE FOR dbo.BatchSequence;
DECLARE @TempTableName NVARCHAR(255) = N'##Temp_' + CAST(@BatchId AS NVARCHAR(20));
DECLARE @sql NVARCHAR(MAX);

-- CREATE TABLE mit dynamischem Namen
SET @sql = N'CREATE TABLE ' + QUOTENAME(@TempTableName) + N' (Id INT, Data NVARCHAR(255))';
EXEC sys.sp_executesql @sql;

-- Nun kann über den gleichen Namen auf die Tabelle zugegriffen werden
SET @sql = N'INSERT INTO ' + QUOTENAME(@TempTableName) + N' VALUES (1, ''Test'')';
EXEC sys.sp_executesql @sql;

-- Nach Abschluss: Tabelle löschen
SET @sql = N'DROP TABLE ' + QUOTENAME(@TempTableName);
EXEC sys.sp_executesql @sql;

🚨 Risiko: Vergessen Sie nicht das „DROP“ – sonst sammeln sich globalen Temp-Tabellen an. Zudem besteht bei einer Fehlerausführung im dynamischen Teil die Gefahr, dass die Tabelle nicht gelöscht wird. Verwenden Sie daher nach Möglichkeit einen TRY...CATCH-Block, um die Aufräumarbeiten zu gewährleisten.

🔄 Daten über Sessions teilen – Die Herausforderung

KOMMUNIKATION

Mit einer globalen temp-Tabelle können verschiedene Sessions auf dieselben Daten zugreifen. Der Clou: Die erzeugende Session muss dafür sorgen, dass der Name der Tabelle den anderen Sessions bekannt ist. Das kann über eine zentrale Steuertabelle, eine Rückgabe des Namens an den Aufrufer, oder durch einen festen (aber dann nicht parallelen) Namen erfolgen.

-- Prozedur, die eine globale Temp-Tabelle erzeugt und deren Namen zurückgibt
CREATE OR ALTER PROCEDURE dbo.CreateGlobalWorkTable
  @WorkId INT,
  @GlobalTableName NVARCHAR(255) OUTPUT
AS
BEGIN
  SET @GlobalTableName = N'##Work_' + CAST(@WorkId AS NVARCHAR(20));
  DECLARE @sql NVARCHAR(MAX) = N'CREATE TABLE ' + QUOTENAME(@GlobalTableName) + N' (Col1 INT, Col2 NVARCHAR(100))';
  EXEC sys.sp_executesql @sql;
END;
GO

-- Aufruf in Session A
DECLARE @TableName NVARCHAR(255);
EXEC dbo.CreateGlobalWorkTable @WorkId = 42, @GlobalTableName = @TableName OUTPUT;

-- Session B kann dann mit dem gleichen @TableName (z. B. '##Work_42') auf die Tabelle zugreifen

📌 Wichtig: Der Name muss absolut eindeutig sein, z. B. durch eine Sequenz oder eine GUID. Ein gleichzeitiger Aufruf mit demselben WorkId würde sonst zu einem Fehler führen, da die Tabelle bereits existiert.

📖 Spaghetti-Code vermeiden – Dynamisches SQL lesbar halten

STIL

Kombinierte dynamische Abfragen mit globalen Temp-Tabellen neigen dazu, unübersichtlich zu werden. Um dem entgegenzuwirken:

Verwenden Sie PRINT @sql, um die generierte Abfrage zu debuggen.
Nutzen Sie Kommentare innerhalb des dynamischen Strings (auch wenn sie nicht im Resultset erscheinen).
Lagern Sie komplexe dynamische Blöcke in Funktionen oder Prozeduren aus.
Setzen Sie auf einheitliche Namenskonventionen für globale Temp-Tabellen: z. B. ##AppName_Zweck_BatchId.

-- Bessere Lesbarkeit durch mehrzeiligen String
DECLARE @sql NVARCHAR(MAX) = N'
-- =========================================================
-- Dynamische Abfrage für Bericht ' + @ReportName + N'
-- Erstellt am ' + CAST(GETDATE() AS NVARCHAR(50)) + N'
-- =========================================================
SELECT 
  Id,
  Name
FROM ' + QUOTENAME(@TableName) + N'
WHERE Active = 1
ORDER BY Name;
';
PRINT @sql;
EXEC sys.sp_executesql @sql;

💡 Best Practice: Binden Sie dynamisches SQL nach Möglichkeit in gespeicherte Prozeduren ein. Die Prozedur kann dann die Logik kapseln, und der Aufrufer muss sich nicht um die korrekte Erstellung der globalen Tabelle kümmern.

🛡️ Sicherheit: Injection durch dynamische ##-Tabellen verhindern

SECURITY

Das größte Risiko bei dynamischem SQL ist die Einschleusung von bösartigem Code. Auch bei globalen Temp-Tabellen kann ein Angreifer über einen unsauber gebildeten Tabellennamen Schaden anrichten (z. B. '##Temp; DROP TABLE Kunden --'). Deshalb:

Immer QUOTENAME() für Objektnamen verwenden. Das maskiert eckige Klammern und schließt den Namen sicher ein.
Niemals Benutzereingaben direkt in den SQL-String übernehmen. Für Werte (nicht Objektnamen) sind Parameter von sp_executesql zu verwenden.
Verwenden Sie für die eindeutige ID der globalen Tabelle keine unsicheren Quellen (z. B. einen Sequence-Generator oder NEWID()).

-- Unsicher (tableName kommt von Benutzereingabe)
SET @sql = 'SELECT * FROM ' + @tableName; -- Angreifer könnte 'Kunden; DROP TABLE ...' einschleusen

-- Sicher (QUOTENAME verhindert Ausbruch)
SET @sql = 'SELECT * FROM ' + QUOTENAME(@tableName);

🚨 Warnung: Auch mit QUOTENAME() ist nicht 100% aller Schadcode möglich – aber die Methode gilt als sicherer Standard. Verwenden Sie sie ausnahmslos für alle dynamischen Objektnamen.

⚡ Performance von dynamischem SQL mit globalen Temp-Tabellen

OPTIMIERUNG

Jedes dynamische SQL, das unterschiedliche Tabellennamen verwendet, erzeugt einen eigenen Ausführungsplan im Cache. Das kann zu Plan‑Cache-Verschmutzung führen, wenn die Namen stark variieren (z. B. ##Temp_1, ##Temp_2 …). Die Folge: Höherer Kompilierungsaufwand, mehr Arbeitsspeicher für Pläne. Daher:

Verwenden Sie wenn möglich einen festen Tabellennamen für die globale Temp-Tabelle – dann wird der Plan wiederverwendet. Dafür müssen Sie aber sicherstellen, dass immer nur eine Session auf diese Tabelle zugreift (Serialisierung).
Alternativ: Nutzen Sie eine permanente Tabelle (aber mit Session-Kennung) oder eine echte Queue.
Bei starker Plan-Variation: Erwägen Sie die Verwendung von OPTION (RECOMPILE) oder der Abfragehinweise für einmalige Abfragen.

-- Beispiel: Fester Name für globale Tabelle (dann nur eine gleichzeitig möglich)
CREATE TABLE ##SharedWork (...);

-- Dynamische Abfrage mit festem Tabellennamen – Plan wird wiederverwendet
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ##SharedWork WHERE SessionId = @sid';
EXEC sys.sp_executesql @sql, N'@sid INT', @sid = 42;

💡 Empfehlung: Wenn Sie tatsächlich viele verschiedene Tabellen benötigen, setzen Sie auf eine echte dauerhafte Tabelle mit einer Spalte BatchId und räumen Sie diese nach der Verarbeitung auf. Das ist meist performanter und sicherer als wilde ##-Tabellen.

✅ Best Practices – Kombination von Dynamic SQL und ##Temp

CHECKLISTE

Immer QUOTENAME() für dynamische Objektnamen (Tabellen, Spalten, Schemas).
Parameter über sp_executesql übergeben, niemals direkt in den String einbetten.
Für jede Ausführung eindeutige Namen für globale Temp-Tabellen verwenden (z. B. mit Sequenz oder NEWID()).
Globale Temp-Tabellen am Ende der Verarbeitung explizit löschen (DROP TABLE).
Prüfen, ob die Tabelle bereits existiert – nutzen Sie OBJECT_ID('##Tabelle', 'U').
Verwenden Sie TRY..CATCH, um auch bei Fehlern das DROP durchzuführen.
Dokumentieren Sie den Zweck der dynamischen Konstruktion – komplexer Code ohne Kommentare wird schnell zum Albtraum.
Ziehen Sie permanente Hilfstabellen mit Batch-Kennung vor, wenn die Parallelität hoch ist.

⚠️ Häufige Fehler und wie man sie vermeidet

TROUBLESHOOTING

Fehler „There is already an object named ‚##…‘ in the database“ → Sie haben denselben globalen Temp-Tabellennamen mehrfach verwendet. Lösen Sie das durch eindeutige Namen oder prüfen Sie vorher mit IF OBJECT_ID('##...') IS NOT NULL DROP TABLE ....
Fehler „Invalid object name ‚##…‘“ → Die Tabelle wurde bereits gelöscht oder von einer anderen Session nicht mehr gefunden. Ursache: Die letzte Session, die auf die Tabelle zugriff, wurde beendet. Verwenden Sie permanente Tabellen, wenn Daten über längere Zeit benötigt werden.
SQL-Injection trotz QUOTENAME? → QUOTENAME sichert nur Objektnamen, nicht Werte. Wenn Sie Werte einfügen, nutzen Sie Parameter.
Sehr lange Laufzeit des dynamischen SQL → Möglicherweise schlechte Planung aufgrund unterschiedlicher Tabellennamen. Überprüfen Sie den Plan Cache und setzen Sie ggf. OPTION (RECOMPILE).
Nicht nachvollziehbare Fehler in dynamischen Strings → Lassen Sie sich den generierten String mit PRINT ausgeben. Kopieren Sie ihn dann in ein Abfragefenster, um ihn zu analysieren.

📌 Ultimativer Tipp: Vermeiden Sie die Kombination, wenn es einen anderen Weg gibt. Oft kann man mit einer normalen temporären Tabelle (#temp) und einem dynamischen Aufruf im selben Batch auskommen. Erst wenn Sie wirklich sitzungsübergreifende Daten benötigen, sind globale Temp-Tabellen sinnvoll.

📌 Fazit – Mit Bedacht kombinieren

RESÜMEE

Dynamisches SQL und globale temporäre Tabellen sind extrem nützliche Werkzeuge, die zusammen hochflexible Lösungen ermöglichen. Sie sollten jedoch mit größter Sorgfalt eingesetzt werden. Halten Sie sich an die Sicherheitsregeln (QUOTENAME, Parameter), vermeiden Sie Namenskollisionen und räumen Sie immer ordentlich auf. In vielen Fällen sind permanente Tabellen mit Batch-Kennung oder der Einsatz von Service Broker die wartbarere Alternative. Wenn Sie sich jedoch für den dynamischen Weg entscheiden, bietet dieser Artikel eine solide Grundlage für eine saubere Implementierung.

💡 Weiterführend: Testen Sie Ihre dynamischen Konstrukte intensiv mit mehreren parallelen Benutzern, um die Auswirkungen auf die TempDB und den Plan Cache zu verstehen.