Dočasné tabulky v Synapse SQL

Článek
06/01/2023

Tento článek obsahuje základní pokyny k používání dočasných tabulek a zdůrazňuje principy dočasných tabulek na úrovni relací v synapse SQL.

Jak vyhrazený fond SQL, tak prostředky bezserverového fondu SQL můžou využívat dočasné tabulky. Bezserverový fond SQL má omezení, která jsou popsána na konci tohoto článku.

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. U Synapse SQL existují dočasné tabulky na úrovni relace. Jsou viditelné pouze pro relaci, ve které byly vytvořeny. Proto se po skončení této relace automaticky vyřadí.

Dočasné tabulky ve vyhrazeném fondu SQL

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í předponou názvu tabulky s příponou #. Pří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 také možné vytvořit CTAS pomocí úplně 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á další výhodu v efektivním využití 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 ale voláte stejnou uloženou proceduru, která vytvoří dočasnou proceduru se stejným názvem, použijte jednoduchou kontrolu před existencí pomocí DROPpříkazu , abyste zajistili, že vaše CREATE TABLE příkazy budou úspěšné.

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

Pro konzistenci kódování je vhodné použít tento vzor pro tabulky i dočasné tabulky. Je také vhodné použít DROP TABLE k odebrání dočasných tabulek, jakmile s nimi skončíte.

Při vývoji uložených procedur je běžné vidět příkazy pro vyřazení, které jsou na konci procedury spojeny dohromady, aby se zajistilo, že se tyto objekty vyčistí.

DROP TABLE #stats_ddl

Modularizace kódu

Dočasné tabulky je možné použít kdekoli v uživatelské relaci. Tato funkce pak může být zneužita k tomu, aby vám pomohla modularizovat kód aplikace. Pro ukázku následující uložená procedura vygeneruje 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 je jedinou akcí, ke které došlo, vytvoření uložené procedury, která vygeneruje #stats_ddl dočasné tabulky. Uložená procedura se #stats_ddl, pokud již existuje. Toto vyřazení zajistí, že nedojde k selhání, pokud se v relaci spustí více než jednou.

Vzhledem k tomu, že na konci uložené procedury není DROP TABLE žádná, po dokončení uložené procedury zůstane vytvořená tabulka a bude ji možné číst mimo uloženou proceduru.

Na rozdíl od jiných SQL Server databází umožňuje Synapse SQL používat dočasnou tabulku mimo proceduru, která ji vytvořila. Dočasné tabulky vytvořené prostřednictvím vyhrazeného fondu SQL je možné použít kdekoli uvnitř relace. V důsledku toho budete mít více modulárního a spravovatelného kódu, jak je znázorněno v následující ukázce:

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

Vyhrazený fond SQL má pro dočasné tabulky několik omezení implementace:

Podporují se pouze dočasné tabulky s oborem relace. Globální dočasné tabulky se nepodporují.
Zobrazení nelze vytvořit u dočasných tabulek.
Dočasné tabulky je možné vytvořit pouze s distribucí hodnot hash nebo kruhového dotazování. Replikovaná dočasná distribuce tabulek se nepodporuje.

Dočasné tabulky v bezserverovém fondu SQL

Dočasné tabulky v bezserverovém fondu SQL se podporují, ale jejich využití je omezené. Není možné je používat v dotazech, které cílí na soubory.

Dočasnou tabulku například nemůžete spojit s daty ze souborů v úložišti. Počet dočasných tabulek je omezený na 100 a jejich celková velikost je omezená na 100 MB.

Další kroky

Další informace o vývoji tabulek najdete v článku Navrhování tabulek pomocí prostředků Synapse SQL .

Share via