Synapse SQL 中的暫存資料表

Tip

Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。現有的專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。

本文提供使用暫存資料表的基本指引，並強調 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 資源中「設計資料表」條目。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-05-03