Synapse SQL 中的暫存資料表

發行項
06/01/2023

本文包含使用暫存資料表的基本指引，並強調說明 Synapse SQL 內工作階段層級暫存資料表的原則。

專用 SQL 集區和無伺服器 SQL 集區資源都可以使用暫存資料表。無伺服器 SQL 集區具有本文最後討論的限制。

暫存資料表

暫存資料表在處理資料時很有用，尤其是具有暫時性中繼結果的轉換期間。使用 Synapse SQL，暫存資料表存在於工作階段層級。只有出現在其建立所在的工作階段。如此一來，當該工作階段結束時，就會自動將其卸除。

專用 SQL 集區中的暫存資料表

在專用 SQL 集區資源中，暫存資料表的結果會寫入本機，而不是遠端儲存體，這是其效能優點。

建立暫存資料表

建立暫存資料表時會在資料表名稱前面加上 #。例如：

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
)

CTAS 也可用來建立暫存資料表，方法完全相同：

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

注意

CTAS 是一個強大的命令，可有效率地使用交易記錄空間是它額外的好處。

捨棄暫存資料表

建立新的工作階段時，不應該存在任何暫存資料表。不過，如果您呼叫同一個預存程序來建立具有相同名稱的暫存資料表，為了確保 CREATE TABLE 陳述式成功執行，可使用簡單的預先存在性檢查搭配 DROP︰

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

為了維持編寫程式碼的一致性，資料表和暫存資料表最好都採用此模式。當您完成使用暫存資料表之後，使用 DROP TABLE 加以移除也是一個很好的做法。

在預存程序開發期間，在程序結尾一併搭配 drop 命令以確保會清除這些物件，也是常見的做法。

DROP TABLE #stats_ddl

模組化程式碼

暫存資料表可在使用者工作階段中的任何位置使用。如此一來，就可以利用這項功能來協助您模組化應用程式程式碼。為了示範，下列預存程序會產生 DDL，根據統計資料名稱來更新資料庫中的所有統計資料：

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

在這個階段中，唯一進行的動作是建立預存程序，以產生 #stats_ddl 暫存資料表。預存程序會卸除 #stats_ddl (如果已存在)。如果此卸除在工作階段中執行一次以上，則可確保不會失敗。

因為預存程序結尾沒有任何 DROP TABLE，當預存程序完成時，會保留建立的資料表，以便能夠從預存程序外部讀取。

相較於其他 SQL Server 資料庫，Synapse SQL 可讓您在建立此資料表的程序之外使用該暫存資料表。工作階段內的任何位置都可以使用透過專用 SQL 集區建立的暫存資料表。因此，您將會有更多模組化且可管理的程式碼，如下列範例所示：

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;

暫存資料表限制

專用 SQL 集區有一些暫存資料表的實作限制：

僅支援工作階段範圍內的暫存資料表。不支援全域暫存資料表。
無法在暫存資料表上建立檢視。
只能使用雜湊或循環配置資源散發來建立暫存資料表。不支援複寫的暫存資料表散發。

無伺服器 SQL 集區中的暫存資料表

支援無伺服器 SQL 集區中的暫存資料表，但其使用方式有限。這些暫存資料表不能用在以檔案為目標的查詢中。

例如，您無法從儲存體中的檔案聯結暫存資料表與資料。暫存資料表的數目限制為 100，而其大小總計限制為 100 MB。

下一步

若要深入了解如何開發資料表，請參閱使用 Synapse SQL 資源設計資料表一文。

共用方式為