Synapse SQL 中的临时表

项目
10/23/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 资源设计表一文。

Share via