SQL Server 临时表和变量系列之对比篇

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: # 摘要 在SQL Server代码编写过程中,经常会有需要临时“暂存”一部分数据结果集,供上下文使用,这个时候,我们有两种选择,即临时表和表变量。这篇文章从以下几个方面来对临时表和表变量进行对比: 创建和析构方式 存储方式 作用域 对事务的支持 性能影响 # 创建和析构方式 临时表和表变量在创建和析构方式上是完全不一样的,在这一节,我们会从以下几点来看看他们的不同。

摘要

在SQL Server代码编写过程中,经常会有需要临时“暂存”一部分数据结果集,供上下文使用,这个时候,我们有两种选择,即临时表和表变量。这篇文章从以下几个方面来对临时表和表变量进行对比:
创建和析构方式
存储方式
作用域
对事务的支持
性能影响

创建和析构方式

临时表和表变量在创建和析构方式上是完全不一样的,在这一节,我们会从以下几点来看看他们的不同。
结构定义
索引创建
DDL
析构方式

结构定义

在上一篇文章SQL Server 临时表和变量系列之概念篇中,我们已经知道了临时表的定义方式是CREATE TABLE,而表变量的定义方式是DECLARE TABLE,在此我们不再过多的累述。让我们重温代码既可:

USE tempdb
GO
-- ************Temp table 
-- If exists named temp table, drop it.
IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO

-- And then create a new one
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  CONSTRAINT DF_tbTable_Indate DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

-- ************table variable
-- we don't need to check existence, declare directly.
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

索引创建

临时表和表变量关于索引的创建方式是完全不一样的。临时表可以定义表结构之中或之后创建索引,而表变量只能在定义的过程中创建。如果在上面结构定义的语句之后,再执行下面的语句,第一个语句可以成功执行,而第二条语句会报错。

-- Create index for temptable, success
CREATE INDEX IX_ProductName
ON #tb_table(ProductName);
GO

-- Try to create index for table variable, failed
CREATE INDEX IX_ProductName
ON @tb_table(ProductName);
GO

执行结果如下:
01.png

DDL

关于临时表和表变量的DDL操作,与索引创建类似。临时表可以在定以后进行任何的DDL操作,而表变量在完成定以后,是完全禁止DDL操作的。假设我们需要为临时表加上一个新的字段ModifiedDate时间字段,同时我们也尝试为表变量添加这个字段定义。

-- Add a new column into temp table
ALTER TABLE #tb_table
ADD ModifiedDate  DATETIME NULL
GO

-- Try to add a new column to table variable
ALTER TABLE @tb_table
ADD ModifiedDate DATETIME NULL
GO

为临时表添加字段成功,而表变量报错,截图如下:
02.png

析构方式

临时表和表变量创建成功,使用完毕以后,系统需要回收这部分资源。在析构方式上也存在差异。针对表变量,SQL Server析构的方式比较简单,在表变量所在的批处理结束后,系统会自动回收资源,无需任何的人为干预。而对于临时表相对比较复杂一些,这里需要分为全局临时表和局部临时表。
全局临时表:以##打头的临时表称为全局临时表,此类型的临时表对所有进程可见,此类型的临时表生命周期是所有使用到全局临时表的连接完全断开后,临时表资源被系统自动回收;当然我们也可以手动析构临时表,方法是使用DROP TABLE语句。
局部临时表:以#打头的临时表称为局部临时表,这种类型的临时表仅当前进程可见,其他进程不可访问,生命周期是随着当前连接进程的关闭而消亡,临时表资源被系统自动回收;相同道理,我们同样可以使用DROP TABLE语句手动回收。

注意:
如果临时表(无论全局还是局部临时表)中存放有大量记录数(比如超过10万条记录数),手动回收临时表资源前,强烈建议使用TRUNCATE TABLE不记录日志的方式删除临时表所有数据,以防止临时表直接DROP操作给tempdb日志文件带来写入压力。

IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE #tb_table
    DROP TABLE #tb_table
END

存储方式

我时常在一些图书和网络上的文章看到很多人都会说临时表是存储在磁盘上,而表变量是存储在内存中。这种说法太过武断,并且是错误的。这一节,我们会从两个方面来讨论临时表和表变量的存储方式,通过这一节,你就知道这个观点错在那里了。
结构定义信息
数据存储

结构定义信息

结构定义信息是指创建临时表或表变量结构的定义信息,比如:约束、索引、表结构等。到底这些定义信息存放在哪里?或者说我们如何获取临时表和表变量的结构定义信息呢?在临时表创建完毕后,我们使用下面的语句可以查看:

USE tempdb
GO
;WITH DATA
AS(
    SELECT 
        *
    FROM sys.objects
    WHERE object_id = object_id('#tb_table')
    UNION ALL
    SELECT *
    FROM sys.objects
    WHERE parent_object_id = object_id('#tb_table')
)
SELECT
    parent_object = OBJECT_NAME(parent_object_id)
    ,name
    ,type_desc
    ,create_date
FROM DATA
ORDER BY create_date DESC

从展示结果来看,临时表的定义信息是存放在Tempdb数据库下的:
03.png

这里有一个非常有趣的问题需要思考下:在创建临时表的定义语句中,我们的临时表名字明明是#tb_table,为什么这里却变成了#tb_table__000000000005呢?答案我们会在“作用域
”这一节来揭晓。
那让我们来看看如何获取表变量的定义,为了看到测试效果,我特意将当前数据修改到master数据库下,然后从Tempdb下去获取表变量的定义信息(其他数据库无法获取到):

-- ************table variable
-- we don't need to check existence, declare directly.
USE master
GO

DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

SELECT TOP 7 
    current_db = db_name(), name, parent_name = (SELECT TOP 1 name FROM tempdb.sys.all_objects WHERE object_id = A.parent_object_id)
    ,type_desc, create_date, [current_date] = getdate()
FROM tempdb.sys.all_objects AS A
ORDER BY create_date DESC

从展示的结果来看,我们成功的从Tempdb下获取到了表变量的定义信息,结果如下:
04.png

数据存储

写到这里,我们已经知道了临时表和表变量的定义信息均是放在Tempdb数据库下的。那么临时表和表变量的数据又是存放在哪里的呢?这一小节,我们要探讨这个问题。
首先我们来看看临时表中的数据的存储位置。我们在SSMS中开启一个连接,执行以下语句:

USE tempdb
GO
SELECT database_name = db_name(),name,type_desc,size = size/128.
FROM sys.database_files

WAITFOR DELAY '00:00:10'

SELECT database_name = db_name(),name,type_desc,size = size/128.
FROM sys.database_files

然后我们在5秒时间内开启另外一个连接,执行下面的语句

USE tempdb
GO
-- If exists named temp table, drop it.
IF OBJECT_ID('tempdb..#tb_dataLocation','U') IS NOT NULL
    DROP TABLE #tb_dataLocation
GO
CREATE TABLE #tb_dataLocation(bigChar char(8000));

DECLARE
    @do int = 1; 
WHILE @do <= 10000
BEGIN
    INSERT INTO #tb_dataLocation VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END

SELECT 
       OBJECT_NAME = object_name(object_id),
       reserved_size = SUM(reserved_page_count)/128.,
       used_pages = SUM(used_page_count),
       pages = SUM(CASE
                  WHEN index_id < 2 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
                  ELSE lob_used_page_count + row_overflow_used_page_count
              END),
       row_count = SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END)
FROM tempdb.sys.dm_db_partition_stats WITH(NOLOCK)
WHERE object_id = object_id('#tb_dataLocation', 'U')
GROUP BY object_id

从第二个连接执行结果展示来看,我们往临时表#tb_dataLocation中插入了10000条数据,总共占用了78.19MB空间。
05.png

我们再来看看第一个连接执行完毕后的结果,tempdb数据库数据文件增长了80MB,这个数字大小和临时表空间占用大小78.19非常接近了(因为我的Tempdb的Filegrowth设置为10MB,数据文件因为临时表数据的插入增长了8次)。
06.png

因此我们可以得出结论,临时表中的数据是存放在Tempdb的磁盘上数据文件中。
接下来,我们看看表变量中的数据到底是存放在哪里的?我们还是新开一个SSMS连接,执行如下语句。这段代码是统计SQL Server缓存(即内存)中BufferPool空间变化情况。

use master
GO
DBCC DROPCLEANBUFFERS

select total_cached_pages = count(1) / 128.
from sys.dm_os_buffer_descriptors b 
where b.database_id = db_id('tempdb')
and is_modified=1

WAITFOR DELAY '00:00:10'

select total_cached_pages = count(1) / 128.
from sys.dm_os_buffer_descriptors b 
where b.database_id = db_id('tempdb')
and is_modified=1

在5秒之内新开另外一个连接,执行下面的语句,表结构和临时表一致,插入的数据记录数也一致。所以,表变量中存放的数据大小也大概在78MB左右:

-- table variables
DECLARE  @tb_dataLocation TABLE(bigChar char(8000));

DECLARE
    @do int = 1; 
WHILE @do <= 10000
BEGIN
    INSERT INTO @tb_dataLocation VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END
GO

让我们看看第一个新开的连接执行的结果情况,从结果来看SQL Server数据缓存增加了78.65减去0.55,约等于78MB左右的内存空间占用,这个大小和表变量数据大小几乎一致。
07.png

因此,我们可以得出结论,表变量数据是存放在SQL Server的缓存中,即内存中(当然也会有例外情况,当SQL Server内存空间不足时,表变量数据会写入磁盘)。

作用域

临时表和表变量的另一个不同是作用域不同。

局部临时表

局部临时表是以#打头的临时表,局部临时表仅当前进程可见,其他进程不可访问,生命周期会随着当前连接进程的关闭而消亡。那么,局部临时表是如何做到仅当前会话可见呢?在局部临时表创建的时候,SQL Server会将局部临时表名后添加一串随机字符来作为局部临时表在系统中的唯一标识符,比如:#tb_table__000000000005(这里我以两个下划线来代替多个下划线),这样可以避免其他进程在创建相同临时表名字的时候导致的命名冲突(比如:两个进程同时执行了使用临时表的存储过程),这个也是“结构定义存储”小节问题的答案。我们怎么来确定这两者是同一个临时表呢?请使用下面的方法:我们先往临时表中插入一条记录,然后分别查询这两个表名字不一样的临时表(其实是同一个表),看看数据是否一样即可。

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES(NEWID(), 0.1, 0.2, 0.3);
SELECT *
FROM #tb_table

SELECT *
FROM [#tb_table___________________________________________________________________________________________________________000000000005] WITH(NOLOCK)

结果展示如下,我们可以看到这两条记录是一模一样的,所以这两个表是指同一个对象。
08.png

全局临时表

以##打头的临时表称为全局,此类型的临时表对所有进程可见,当前进程和其他进程均可访问,生命周期是所有使用到全局临时表的连接完全关闭后,临时表消亡。关于全局临时表作用域的实例演示我们已经在文章SQL Server 表变量和临时表系列之概念篇中“临时表的使用”章节涉及到了,在此不再累述。

表变量

表变量的作用域和变量作用域是一致的,都是当前批处理,因为从根本上来讲表变量就是一种特殊的本地变量。在当前连接进程关闭以后,表变量会被SQL Server数据库系统自动回收而无需用户干预。

对事务支持

按照我们前面文章的分析,临时表数据是存放在tempdb物理文件的磁盘上,是一种特殊的表结构;表变量数据是基于内存存放的一种特殊变量结构。临时表对事务的支持和普通表对象保持一致;而表变量仅在更新表的时候有事务,也就是说表变量仅在操作这张表的DML操作时候支持事务,其他情况不支持事务。
让我们来看下面的例子,代码如下:

--***************Transaction Support

IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO

-- create temptable
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

-- declare temp variable
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

-- open a transaction
BEGIN TRAN

-- insert one record
INSERT INTO #tb_table 
    OUTPUT INSERTED.ProductName
    INTO @tb_table(ProductName)
SELECT NEWID();

SELECT comment = 'temp table',* FROM #tb_table WITH(NOLOCK)
SELECT comment = 'table variable',* FROM @tb_table

ROLLBACK

SELECT comment = 'temp table',* FROM #tb_table WITH(NOLOCK)
SELECT comment = 'table variable',* FROM @tb_table

执行结果如下:
09.png

从这个结果截图,我们可以得出:临时表支持用户事务,表变量不支持用户事务。

性能影响

临时表和表变量均可以用作临时数据暂存媒介,具有相同的功效。但是,性能有时会有天壤之别。究其原因,我的分析是下面两个主要原因:
统计信息
动态SQL

统计信息

根据之前的经验,我们不止一次遇到过用户反馈,使用表变量的存储过程性能远远不及使用临时表的存储过程(当然其他代码保持一致的,仅将表变量替换为临时表)。按理讲,表变量存放在内存中,应该比临时表存放在磁盘读写效果高,而得出这样的结论和大多数人的认识相违背。究其原因就在于本节要讨论的主题 - 统计信息。按照一般常理,大部分人是不会对表变量创建主键、索引的,这个有可能是因为表变量只能在定义时创建主键和索引导致很多人忽略了,又或者是很多人根本没有意识到要为表变量建立索引。但是,对于临时表,大家习惯于创建主键、索引的,这就导致了表变量不存在任何的统计信息,而临时表有完整的统计信息。根据之前的文章SQL Server幕后英雄 - 统计信息我们清楚的知道统计信息会左右SQL Server的执行计划评估,和SQL执行效率息息相关,对SQL语句的查询性能起着至关重要的作用。
查看表变量的统计信息:

DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

DECLARE
    @table_variable_id bigint
    ;
SELECT TOP 1 @table_variable_id = object_id
FROM tempdb.sys.all_objects AS A
WHERE parent_object_id = 0
ORDER BY create_date DESC

SELECT  
    statistics_name = st.name
    ,table_name = OBJECT_NAME(st.object_id)
    ,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    sys.stats AS st WITH(NOLOCK) 
        INNER JOIN sys.stats_columns AS stc WITH(NOLOCK)
            ON st.object_id = stc.object_id  
            AND st.stats_id = stc.stats_id 
WHERE st.object_id = @table_variable_id

执行结果如下:
10.png

动态SQL

临时表可以在调用动态SQL之前定义,在动态SQL中使用;而表变量只能在动态SQL中定义,否则会报告错误异常。如下事例:

-- ************Dynamic SQL

-- temp table
IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO

-- create temptable
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO #tb_table SELECT NEWID();

DECLARE
    @sql nvarchar(max)
    ;

SET
    @sql = N'SELECT * FROM #tb_table WHERE RowId = @RowId'
    ;

EXEC sys.sp_executesql @sql,N'@RowID int', @RowID = 1

-- table variable
SET @sql = N'
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO @tb_table SELECT NEWID();

SELECT * FROM @tb_table WHERE RowId = @RowId
';

EXEC sys.sp_executesql @sql,N'@RowID int', @RowID = 1
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
92 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
58 6
|
4月前
|
SQL 运维 分布式计算
DataWorks产品使用合集之ODPPS中如何使用SQL查询从表中获取值并将其赋值给临时变量以供后续使用
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
278 1
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
406 0
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
258 3
|
4月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
4月前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
110 0