Temporäre Tabellen in Synapse SQL

Dieser Artikel enthält wichtige Anleitungen für die Verwendung temporärer Tabellen und hebt die Prinzipien temporärer Tabellen auf Sitzungsebene in Synapse SQL hervor.

Sowohl die dedizierten SQL-Pool- als auch serverlose SQL-Poolressourcen können temporäre Tabellen verwenden. Der Serverlose SQL-Pool weist Einschränkungen auf, die am Ende dieses Artikels erläutert werden.

Temporäre Tabellen

Temporäre Tabellen sind beim Verarbeiten von Daten nützlich, insbesondere während der Transformation, bei der die Zwischenergebnisse vorübergehend sind. Bei Synapse SQL sind temporäre Tabellen auf Sitzungsebene vorhanden. Sie sind nur für die Sitzung sichtbar, in der sie erstellt wurden. Daher werden sie automatisch verworfen, wenn diese Sitzung endet.

Temporäre Tabellen im dedizierten SQL-Pool

In der dedizierten SQL-Poolressource bieten temporäre Tabellen einen Leistungsvorteil, da ihre Ergebnisse nicht in Remotespeicher, sondern lokal geschrieben werden.

Erstellen einer temporären Tabelle

Temporäre Tabellen werden erstellt, indem Dem Tabellennamen ein #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
)

Mit einem CTAS können temporäre Tabellen auch nach genau demselben Ansatz 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]
)
;

Temporäre Tabellen ablegen

Wenn eine neue Sitzung erstellt wird, 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

Um Konsistenz zu codieren, empfiehlt es sich, 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, dass die Dropbefehle am Ende einer Prozedur gebündelt sind, um sicherzustellen, dass diese Objekte bereinigt werden.

DROP TABLE #stats_ddl

Modularisieren von Code

Temporäre Tabellen können an einer beliebigen Stelle in einer Benutzersitzung verwendet werden. Diese Funktion kann dann ausgenutzt werden, damit Sie Ihren Anwendungscode modularisieren können. 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 aufgetreten ist, die Erstellung einer gespeicherten Prozedur, die die temporäre Tabelle "#stats_ddl" generiert. Die gespeicherte Prozedur löscht „#stats_ddl“, wenn sie bereits vorhanden ist. Durch diesen Drop wird sichergestellt, dass es nicht fehlschlägt, wenn es innerhalb einer Sitzung mehr als einmal ausgeführt wird.

Da am Ende der gespeicherten Prozedur kein DROP TABLE vorhanden ist, bleibt die erstellte Tabelle nach Abschluss der Prozedur bestehen und kann außerhalb der gespeicherten Prozedur gelesen werden.

Im Gegensatz zu anderen SQL Server-Datenbanken ermöglicht Synapse SQL die verwendung der temporären Tabelle außerhalb der Prozedur, die sie erstellt hat. Die über dedizierten SQL-Pool erstellten temporären Tabellen können überall in der Sitzung verwendet werden. Daher haben Sie modulareren und verwaltbaren Code, wie im folgenden Beispiel veranschaulicht:

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 für temporäre Tabellen

Dedizierter SQL-Pool hat einige Implementierungseinschränkungen für temporäre Tabellen:

• Es werden nur sitzungsspezifische temporäre Tabellen unterstützt. Globale temporäre Tabellen werden nicht unterstützt.
• Ansichten können in temporären Tabellen nicht erstellt werden.
• Temporäre Tabellen können nur mit Hash- oder Roundrobinverteilung erstellt werden. Replizierte temporäre Tabellenverteilung wird nicht unterstützt.

Temporäre Tabellen im serverlosen SQL-Pool

Temporäre Tabellen im serverlosen SQL-Pool werden unterstützt, ihre Verwendung ist jedoch eingeschränkt. Sie können nicht in Abfragen verwendet werden, die auf Dateien abzielen.

Sie können z. B. keine temporäre Tabelle mit Daten aus Dateien im Speicher verknüpfen. Die Anzahl temporärer Tabellen ist auf 100 begrenzt, und ihre Gesamtgröße ist auf 100 MB begrenzt.

Nächste Schritte

Weitere Informationen zum Entwickeln von Tabellen finden Sie im Artikel " Entwerfen von Tabellen mithilfe des Sql-Ressourcenartikels von Synapse ".