Dočasné tabulky ve vyhrazeném fondu SQL ve službě Azure Synapse Analytics

2025-04-02

Tento článek obsahuje zásadní návod k používání dočasných tabulek a zdůrazňuje zásady dočasných tabulek na úrovni relace.

Použití informací v tomto článku vám může pomoct s modularizací kódu, což zlepšuje opětovnou použitelnost i snadnost údržby.

Co jsou dočasné tabulky?

Dočasné tabulky jsou užitečné při zpracování dat, zejména při transformaci, kde jsou přechodné výsledky. V dedikovaném SQL fondu existují dočasné tabulky pouze na úrovni relace.

Dočasné tabulky jsou viditelné pouze pro relaci, ve které byly vytvořeny, a při zavření relace se automaticky zahodí.

Dočasné tabulky nabízejí výhodu výkonu, protože jejich výsledky se zapisují do místního, nikoli vzdáleného úložiště.

Dočasné tabulky ve vyhrazeném SQL fondu

Dočasné tabulky ve vyhrazeném prostředku fondu SQL nabízejí výhodu výkonu, protože jejich výsledky se zapisují do místního úložiště, nikoli do vzdáleného úložiště.

Vytvoření dočasné tabulky

Dočasné tabulky se vytvářejí tak, že se k názvu tabulky připojí předpona #. Například:

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
)

Dočasné tabulky je možné vytvořit také pomocí CTAS pomocí stejného přístupu:

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

Poznámka:

CTAS je výkonný příkaz a má přidanou výhodu efektivního využívání prostoru transakčních protokolů.

Vyřazení dočasných tabulek

Při vytvoření nové relace by neměly existovat žádné dočasné tabulky.

Pokud voláte stejnou uloženou proceduru, která vytvoří dočasnou proceduru se stejným názvem, abyste měli jistotu, že příkazy CREATE TABLE budou úspěšné, můžete jednoduchou kontrolu existence s DROP použít jako v následujícím příkladu:

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

Pro konzistenci kódování je vhodné tento vzor použít pro tabulky i dočasné tabulky. Je také dobrý nápad použít DROP TABLE k odebrání dočasných tabulek, až je přestanete používat ve svém kódu.

Ve vývoji uložených procedur je běžné, že se příkazy drop seskupí dohromady na konci procedury, aby se zajistilo, že se tyto objekty vyčistí.

DROP TABLE #stats_ddl

Modularizace kódu

Vzhledem k tomu, že dočasné tabulky se dají zobrazit kdekoli v uživatelské relaci, můžete tuto funkci využít k tomu, abyste mohli kód aplikace modularizovat.

Například následující uložená procedura generuje DDL pro aktualizaci všech statistik v databázi podle názvu statistiky:

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

V této fázi jedinou akcí, ke které došlo, je vytvoření uložené procedury, která generuje dočasnou tabulku, #stats_ddl, s příkazy DDL.

Tato uložená procedura odstraní existující #stats_ddl, aby bylo zajištěno, že při spuštění více než jednou v rámci relace nedojde k selhání.

Vzhledem k tomu, že na konci uložené procedury neexistuje žádná DROP TABLE, ponechá po dokončení uložené procedury vytvořenou tabulku, aby ji bylo možné číst mimo uloženou proceduru.

Ve vyhrazeném fondu SQL, na rozdíl od jiných databází SQL Serveru, je možné použít dočasnou tabulku mimo proceduru, která ji vytvořila. Dočasné tabulky vyhrazeného SQL fondu je možné použít kdekoli uvnitř relace. Tato funkce může vést k modulárnímu a spravovatelnému kódu jako v následujícím příkladu:

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;

Dočasná omezení tabulek

Dedikovaný fond SQL má několik omezení při použití dočasných tabulek. V současné době se podporují pouze dočasné tabulky s vymezeným oborem relace. Globální dočasné tabulky nejsou podporované.

Zobrazení se také nedají vytvořit v dočasných tabulkách. Dočasné tabulky lze vytvořit pouze s využitím hash nebo round robin distribuce. Distribuce replikovaných dočasných tabulek není podporována.

Další kroky

Další informace o vývoji tabulek najdete v článku Navrhování tabulek pomocí vyhrazeného fondu SQL.

Sdílet prostřednictvím