Leer en inglés

Compartir a través de

Synapse SQL 中的临时表

本文包含有关使用临时表的基本指南,并重点介绍了 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]
)
;

Nota

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 资源文章中的“设计表 ”。