Temporäre Tabellen in Synapse SQL
Dieser Artikel enthält wichtige Anleitungen zur Verwendung von temporären Tabellen. Zudem werden die Grundsätze von temporären Tabellen auf Sitzungsebene in Synapse SQL behandelt.
Die Ressourcen sowohl des dedizierten SQL-Pools als auch des serverlosen SQL-Pools können temporäre Tabellen verwenden. Beim serverlosen SQL-Pool gibt es Einschränkungen, die am Ende dieses Artikels erläutert werden.
Temporäre Tabellen
Temporäre Tabellen sind nützlich bei der Verarbeitung von Daten – vor allem bei Transformationen, bei denen die Zwischenergebnisse vorübergehend sind. Bei Synapse SQL befinden sich temporäre Tabellen auf Sitzungsebene. Sie sind nur für die Sitzung sichtbar, in der sie erstellt wurden. Daher werden sie automatisch gelöscht, wenn diese Sitzung abgemeldet wird.
Temporäre Tabellen im dedizierten SQL-Pool
In der Ressource des dedizierten SQL-Pools bieten temporäre Tabellen einen Leistungsvorteil, weil deren Ergebnisse nicht in den Remotespeicher, sondern in den lokalen Speicher geschrieben werden.
Erstellen einer temporären Tabelle
Temporäre Tabellen werden erstellt, indem dem Tabellennamen # als Präfix vorangestellt wird. Beispiel:
CREATE TABLE #stats_ddl
(
[schema_name] NVARCHAR(128) NOT NULL
, [table_name] NVARCHAR(128) NOT NULL
, [stats_name] NVARCHAR(128) NOT NULL
, [stats_is_filtered] BIT NOT NULL
, [seq_nmbr] BIGINT NOT NULL
, [two_part_name] NVARCHAR(260) NOT NULL
, [three_part_name] NVARCHAR(400) NOT NULL
)
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
, HEAP
)
Temporäre Tabellen können auch mit CTAS auf genau die gleiche Weise erstellt werden:
CREATE TABLE #stats_ddl
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
, HEAP
)
AS
(
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[has_filter] AS [stats_is_filtered]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name]
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name]
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
GROUP BY
sm.[name]
, tb.[name]
, st.[name]
, st.[filter_definition]
, st.[has_filter]
)
;
Hinweis
CTAS ist ein leistungsfähiger Befehl und bietet den zusätzlichen Vorteil, dass er den Speicherplatz für das Transaktionsprotokoll effizient verwendet.
Löschen temporärer Tabellen
Beim Erstellen einer neuen Sitzung sollten keine temporären Tabellen vorhanden sein. Wenn Sie jedoch dieselbe gespeicherte Prozedur aufrufen, die eine temporäre Tabelle mit dem gleichen Namen erstellt, können Sie mit einer einfachen Überprüfung auf das Vorhandensein per DROP sicherstellen, dass Ihre CREATE TABLE-Anweisungen erfolgreich sind:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
In Bezug auf die Codekonsistenz ist es eine bewährte Methode, dieses Muster sowohl für Tabellen als auch für temporäre Tabellen zu verwenden. Es empfiehlt sich auch, mit DROP TABLE temporäre Tabellen zu entfernen, wenn diese nicht mehr benötigt werden.
Bei der Entwicklung gespeicherter Prozeduren ist es üblich, die Befehle zum Löschen am Ende einer Prozedur zu bündeln, um sicherzustellen, dass diese Objekte bereinigt werden.
DROP TABLE #stats_ddl
Modularisieren von Code
Temporäre Tabellen können an beliebiger Stelle in einer Benutzersitzung verwendet werden. Diese Funktion kann dann genutzt werden, um Ihnen bei der Modularisierung Ihres Anwendungscodes zu helfen. Die folgende gespeicherte Prozedur generiert zum Veranschaulichen z. B. DDL-Code, um alle Statistiken in der Datenbank nach Statistiknamen zu aktualisieren:
CREATE PROCEDURE [dbo].[prc_sqldw_update_stats]
( @update_type tinyint -- 1 default 2 fullscan 3 sample 4 resample
,@sample_pct tinyint
)
AS
IF @update_type NOT IN (1,2,3,4)
BEGIN;
THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;
IF @sample_pct IS NULL
BEGIN;
SET @sample_pct = 20;
END;
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
CREATE TABLE #stats_ddl
WITH
(
DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
sm.[name] AS [schema_name]
, tb.[name] AS [table_name]
, st.[name] AS [stats_name]
, st.[has_filter] AS [stats_is_filtered]
, ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) AS [seq_nmbr]
, QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [two_part_name]
, QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name]) AS [three_part_name]
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id] = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id] = sc.[stats_id]
AND st.[object_id] = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id] = co.[column_id]
AND sc.[object_id] = co.[object_id]
JOIN sys.tables AS tb ON co.[object_id] = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id] = sm.[schema_id]
WHERE 1=1
AND st.[user_created] = 1
GROUP BY
sm.[name]
, tb.[name]
, st.[name]
, st.[filter_definition]
, st.[has_filter]
)
SELECT
CASE @update_type
WHEN 1
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
WHEN 2
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
WHEN 3
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
WHEN 4
THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
END AS [update_stats_ddl]
, [seq_nmbr]
FROM #stats_ddl
;
GO
In dieser Phase ist die einzige Aktion, die durchgeführt wurde, die Erstellung einer gespeicherten Prozedur, die die temporäre Tabelle „#stats_ddl“ erzeugt. Die gespeicherte Prozedur löscht „#stats_ddl“, wenn sie bereits vorhanden ist. Dieser Vorgang stellt sicher, dass kein Fehler auftritt, wenn er mehr als einmal innerhalb einer Sitzung ausgeführt wird.
Da am Ende der gespeicherten Prozedur keine DROP TABLE-Anweisung vorhanden ist, wird die erstellte Tabelle nach Abschluss der gespeicherten Prozedur beibehalten, und sie kann außerhalb der gespeicherten Prozedur gelesen werden.
Im Gegensatz zu anderen SQL Server-Datenbanken können Sie mit Synapse SQL die temporäre Tabelle außerhalb der Prozedur verwenden, die sie erstellt hat. Die temporären Tabellen, die über den dedizierten SQL-Pool erstellt wurden, können überall innerhalb der Sitzung verwendet werden. Daher verfügen Sie über mehr modularen und verwaltbaren Code, wie im folgenden Beispiel gezeigt:
EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;
DECLARE @i INT = 1
, @t INT = (SELECT COUNT(*) FROM #stats_ddl)
, @s NVARCHAR(4000) = N''
WHILE @i <= @t
BEGIN
SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);
PRINT @s
EXEC sp_executesql @s
SET @i+=1;
END
DROP TABLE #stats_ddl;
Einschränkungen der temporären Tabelle
Beim dedizierten SQL-Pool gibt es einige Implementierungseinschränkungen für temporäre Tabellen:
- Nur temporäre Tabellen werden für den Sitzungsbereich unterstützt. Globale temporäre Tabellen werden nicht unterstützt.
- Sichten können nicht in temporären Tabellen erstellt werden.
- Temporäre Tabellen können nur mit Hash- oder Roundrobin-Verteilung erstellt werden. Die Verteilung replizierter temporärer Tabellen wird nicht unterstützt.
Temporäre Tabellen im serverlosen SQL-Pool
Temporäre Tabellen im serverlosen SQL-Pool werden zwar unterstützt, ihre Verwendung ist aber begrenzt. Sie können nicht in Abfragen verwendet werden, die auf Dateien ausgerichtet sind.
Sie können z. B. eine temporäre Tabelle nicht mit Daten aus Dateien im Speicher verbinden. Die Anzahl der temporären Tabellen ist auf 100 begrenzt, und ihre Gesamtgröße ist auf 100 MB beschränkt.
Nächste Schritte
Weitere Informationen zum Entwickeln von Tabellen finden Sie im Artikel Entwerfen von Tabellen mithilfe von Synapse SQL-Ressourcen.