Nota
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
本文包含有关使用临时表的基本指南,并重点介绍了 Synapse SQL 中的会话级别临时表的原则。
专用 SQL 池和无服务器 SQL 池资源都可以利用临时表。 无服务器 SQL 池具有本文末尾讨论的限制。
临时表在处理数据时非常有用,尤其是在中间结果暂时性转换期间。 使用 Synapse 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 池中的临时表,但它们的用途有限。 它们不能在以文件为查询目标的查询中使用。
例如,你无法将临时表与存储中的文件数据进行连接。 临时表的数量限制为 100,其总大小限制为 100 MB。
若要详细了解如何开发表,请参阅 Synapse SQL 资源文章中的“设计表 ”。