Tabele tymczasowe w usłudze Synapse SQL

Ten artykuł zawiera podstawowe wskazówki dotyczące używania tabel tymczasowych i wyróżnia zasady tabel tymczasowych na poziomie sesji w usłudze Synapse SQL.

Zarówno dedykowana pula SQL, jak i zasoby bezserwerowej puli SQL mogą korzystać z tabel tymczasowych. Bezserwerowa pula SQL ma ograniczenia omówione na końcu tego artykułu.

Tabele tymczasowe

Tabele tymczasowe są przydatne podczas przetwarzania danych, zwłaszcza podczas przekształcania, gdy wyniki pośrednie są przejściowe. W przypadku usługi Synapse SQL tabele tymczasowe istnieją na poziomie sesji. Są one widoczne tylko dla sesji, w której zostały utworzone. W związku z tym są one automatycznie porzucane po zakończeniu tej sesji.

Tabele tymczasowe w dedykowanej puli SQL

W dedykowanym zasobie puli SQL tabele tymczasowe oferują korzyść wydajności, ponieważ ich wyniki są zapisywane w magazynie lokalnym, a nie zdalnym.

Tworzenie tabeli tymczasowej

Tabele tymczasowe są tworzone poprzez dodanie prefiksu do nazwy tabeli z #. Na przykład:

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
)

Tabele tymczasowe można również utworzyć przy użyciu CTAS, stosując dokładnie to samo podejście.

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

Uwaga

CTAS jest zaawansowanym poleceniem, i ma dodatkową zaletę efektywnego korzystania z przestrzeni dziennika transakcji.

Usuwanie tabel tymczasowych

Po utworzeniu nowej sesji nie powinny istnieć żadne tabele tymczasowe. Jeśli jednak wywołujesz tę samą procedurę składowaną, która tworzy tymczasową tabelę o tej samej nazwie, aby upewnić się, że instrukcje CREATE TABLE zakończyły się pomyślnie, użyj prostego sprawdzenia wcześniejszego istnienia za pomocą polecenia DROP.

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

W celu zapewnienia spójności kodowania dobrym rozwiązaniem jest użycie tego wzorca zarówno dla tabel, jak i tabel tymczasowych. Również dobrym pomysłem jest użycie DROP TABLE do usunięcia tabel tymczasowych po zakończeniu pracy z nimi.

W przypadku opracowywania procedur składowanych często spotyka się polecenia DROP zgrupowane razem na końcu procedury, aby upewnić się, że te obiekty są usunięte.

DROP TABLE #stats_ddl

Modularyzacja kodu

Tabele tymczasowe mogą być używane w dowolnym miejscu w sesji użytkownika. Ta możliwość może być następnie wykorzystana, aby ułatwić modularyzowanie kodu aplikacji. Aby to zademonstrować, poniższa procedura składowana generuje DDL w celu zaktualizowania wszystkich statystyk w bazie danych według nazwy statystyki:

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

Na tym etapie jedynym działaniem, które miało miejsce, jest utworzenie procedury składowanej, która tworzy tymczasową tabelę #stats_ddl. Procedura składowana usuwa #stats_ddl, jeśli już istnieje. Ten mechanizm gwarantuje, że nie zawiedzie, jeśli zostanie uruchomiony więcej niż raz w ramach jednej sesji.

Ponieważ na końcu procedury składowanej nie istnieje DROP TABLE, po jej zakończeniu utworzona tabela pozostaje i można ją odczytać poza tą procedurą.

W przeciwieństwie do innych baz danych programu SQL Server usługa Synapse SQL umożliwia używanie tabeli tymczasowej poza procedurą, która ją utworzyła. Tabele tymczasowe utworzone za pośrednictwem dedykowanej puli SQL mogą być używane w dowolnym miejscu w sesji. W związku z tym będziesz mieć bardziej modułowy i możliwy do zarządzania kod, jak pokazano w poniższym przykładzie:

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;

Tymczasowe ograniczenia tabeli

Dedykowana pula SQL ma kilka ograniczeń implementacji dla tabel tymczasowych:

Obsługiwane są tylko tabele tymczasowe o zakresie sesji. Globalne tabele tymczasowe nie są obsługiwane.
Nie można tworzyć widoków w tabelach tymczasowych.
Tabele tymczasowe można tworzyć tylko przy użyciu rozkładu skrótu lub rozkładu cyklicznego. Replikowana tymczasowa dystrybucja tabel nie jest obsługiwana.

Tabele tymczasowe w bezserwerowej puli SQL

Tabele tymczasowe w bezserwerowej puli SQL są obsługiwane, ale ich użycie jest ograniczone. Nie można ich używać w zapytaniach skierowanych do plików.

Na przykład nie można dołączyć tabeli tymczasowej z danymi z plików w magazynie. Liczba tabel tymczasowych jest ograniczona do 100, a ich całkowity rozmiar jest ograniczony do 100 MB.

Następne kroki

Aby dowiedzieć się więcej na temat tworzenia tabel, zobacz artykuł Projektowanie tabel przy użyciu zasobów usługi Synapse SQL .

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-04-03

Udostępnij przez

Tabele tymczasowe w usłudze Synapse SQL