Temporära tabeller i Synapse SQL

Artikel
06/01/2023

Den här artikeln innehåller viktig vägledning för att använda temporära tabeller och belyser principerna för temporära tabeller på sessionsnivå i Synapse SQL.

Både den dedikerade SQL-poolen och serverlösa SQL-poolresurser kan använda tillfälliga tabeller. Serverlös SQL-pool har begränsningar som beskrivs i slutet av den här artikeln.

Temporära tabeller

Temporära tabeller är användbara när du bearbetar data, särskilt under transformering där mellanliggande resultat är tillfälliga. Med Synapse SQL finns temporära tabeller på sessionsnivå. De är bara synliga för den session där de skapades. Därför tas de bort automatiskt när sessionen avslutas.

Temporära tabeller i en dedikerad SQL-pool

I den dedikerade SQL-poolresursen erbjuder temporära tabeller en prestandafördel eftersom deras resultat skrivs till lokal i stället för till fjärrlagring.

Skapa en tillfällig tabell

Temporära tabeller skapas genom prefixet för tabellnamnet med en #. Exempel:

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ära tabeller kan också skapas med exakt CTAS samma metod:

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]
)
;

Anteckning

CTAS är ett kraftfullt kommando och har den extra fördelen att det är effektivt att använda transaktionsloggutrymmet.

Ta bort temporära tabeller

När en ny session skapas bör det inte finnas några temporära tabeller. Men om du anropar samma lagrade procedur som skapar en tillfällig med samma namn använder CREATE TABLE du en enkel kontroll DROPmed :

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

För kodkonsekvens är det en bra idé att använda det här mönstret för både tabeller och temporära tabeller. Det är också en bra idé att använda DROP TABLE för att ta bort temporära tabeller när du är klar med dem.

I utvecklingen av lagrade procedurer är det vanligt att se de nedrullningsbara kommandon som paketeras tillsammans i slutet av en procedur för att säkerställa att dessa objekt rensas.

DROP TABLE #stats_ddl

Modularisera kod

Temporära tabeller kan användas var som helst i en användarsession. Den här funktionen kan sedan utnyttjas för att hjälpa dig att modularisera programkoden. För att demonstrera genererar följande lagrade procedur DDL för att uppdatera all statistik i databasen efter statistiknamn:

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

I det här skedet är den enda åtgärd som har inträffat att skapa en lagrad procedur som genererar den #stats_ddl temporära tabellen. Den lagrade proceduren släpper #stats_ddl om den redan finns. Den här minskningen säkerställer att den inte misslyckas om den körs mer än en gång inom en session.

Eftersom det inte finns någon DROP TABLE i slutet av den lagrade proceduren finns den skapade tabellen kvar och kan läsas utanför den lagrade proceduren när den lagrade proceduren har slutförts.

Till skillnad från andra SQL Server databaser kan du med Synapse SQL använda den temporära tabellen utanför proceduren som skapade den. De temporära tabeller som skapas via en dedikerad SQL-pool kan användas var som helst i sessionen. Därför har du mer modulär och hanterbar kod, vilket visas i exemplet nedan:

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;

Begränsningar för temporära tabeller

Den dedikerade SQL-poolen har några implementeringsbegränsningar för temporära tabeller:

Endast temporära tabeller med sessionsomfång stöds. Globala temporära tabeller stöds inte.
Det går inte att skapa vyer i temporära tabeller.
Temporära tabeller kan bara skapas med hash- eller resursallokeringsdistribution. Replikerad tillfällig tabelldistribution stöds inte.

Temporära tabeller i en serverlös SQL-pool

Temporära tabeller i en serverlös SQL-pool stöds, men användningen är begränsad. De kan inte användas i frågor riktade mot filer.

Du kan till exempel inte ansluta till en tillfällig tabell med data från filer i lagringen. Antalet temporära tabeller är begränsat till 100 och deras totala storlek är begränsad till 100 MB.

Nästa steg

Mer information om hur du utvecklar tabeller finns i artikeln Designa tabeller med Hjälp av Synapse SQL-resurser .

Dela via