Tillfälliga tabeller i en dedikerad SQL-pool i Azure Synapse Analytics

2025-04-11

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

Med hjälp av informationen i den här artikeln kan du modularisera din kod, vilket förbättrar både återanvändning och enkelt underhåll.

Vad är temporära tabeller?

Temporära tabeller är användbara vid bearbetning av data, särskilt under transformering där mellanliggande resultat är tillfälliga. I en dedikerad SQL-pool finns tillfälliga tabeller på sessionsnivå.

Temporära tabeller är bara synliga för sessionen där de skapades och tas bort automatiskt när sessionen stängs.

Temporära tabeller erbjuder en prestandaförmån eftersom deras resultat skrivs till lokal i stället för fjärrlagring.

Temporära tabeller i en dedikerad SQL-pool

I den dedikerade SQL-poolresursen erbjuder tillfälliga tabeller en prestandaförmån eftersom deras resultat skrivs till lokal i stället för fjärrlagring.

Skapa en tillfällig tabell

Temporära tabeller skapas genom att prefixera tabellnamnet med en #. Till 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
)

Tillfälliga tabeller kan också skapas med en CTAS genom att använda exakt 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]
)
;

Anmärkning

CTAS är ett kraftfullt kommando och har den extra fördelen att vara effektiv i sin användning av transaktionsloggutrymme.

Ta bort temporära tabeller

När en ny session skapas ska det inte finnas några tillfälliga tabeller.

Om du anropar samma lagrade procedur, som skapar en tillfällig med samma namn, för att säkerställa att dina CREATE TABLE instruktioner lyckas, kan en enkel förhandskontroll med en DROP användas som i följande exempel:

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

För kodningskonsekvens ä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 koden.

I utvecklingen av lagrade procedurer är det vanligt att se DROP-kommandon som samlas i slutet av en lagrad procedur för att säkerställa att dessa objekt tas bort.

DROP TABLE #stats_ddl

Modularisera kod

Eftersom tillfälliga tabeller kan ses var som helst i en användarsession kan den här funktionen användas för att hjälpa dig att modularisera programkoden.

Följande lagrade procedur genererar till exempel 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 skapandet av en lagrad procedur som genererar en tillfällig tabell, #stats_ddl, med DDL-instruktioner.

Den här lagrade proceduren tar bort en befintlig #stats_ddl för att säkerställa att den inte misslyckas om den körs mer än en gång inom en session.

Men eftersom det inte finns någon DROP TABLE i slutet av den lagrade proceduren, när den lagrade proceduren är klar, lämnar den skapade tabellen så att den kan läsas utanför den lagrade proceduren.

Till skillnad från andra SQL Server-databaser i en dedikerad SQL-pool är det möjligt att använda den tillfälliga tabellen utanför proceduren som skapade den. Dedikerade temporära SQL-pooltabeller kan användas var som helst i sessionen. Den här funktionen kan leda till mer modulär och hanterbar kod som i följande exempel:

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;

Temporära tabellbegränsningar

Dedikerad SQL-pool medför ett par begränsningar när du implementerar tillfälliga tabeller. För närvarande stöds endast temporära tabeller med sessionsomfattning. Globala temporära tabeller stöds inte.

Det går inte heller att skapa vyer i temporära tabeller. Tillfälliga tabeller kan bara skapas med hash- eller round-robin-distribution. Replikerad tillfällig tabelldistribution stöds inte.

Nästa steg

Mer information om hur du utvecklar tabeller finns i artikeln Designa tabeller med dedikerad SQL-pool .

Feedback

Var den här sidan till hjälp?