备案控制台
探索云世界
开发者社区 数据库 文章 正文

临时表与表变量深入探究

2017-10-10 1005
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

临时表或表变量我们一般用来充当中间结果集，很多时候都在用，但真正了解他们之间的区别的人还是很少的，网上流传的说法也不甚统一，所以今天我就做一个实验，让我们看看临时表和表变量的区别，以及他们各自的用途。

 

执行以下语句，对测试环境做准备

DBCC DROPCLEANBUFFERS  --从缓冲池中删除所有清除缓冲区
DBCC FREEPROCCACHE   --清除计划缓存
CHECKPOINT    --写入MDF中

 

1） 关于存储

表变量在内存中，是否真的不写磁盘，不会造成任何IO开销？

复制代码
复制代码 
use tempdb  
exec sp_spaceused

--database_name    database_size    unallocated space
--tempdb        8.50 MB            6.75 MB

--tempdb数据库占用8.50M，未用空間6.75M

use TestDB

CREATE TABLE #Table3(id int,  AtypeId char(1024))

declare @count int = 50
INSERT INTO #Table3(id, atypeid)
SELECT TOP(@count) 1 as id, 'sss' FROM GraspFZDRPWrite001.dbo.BillType  --随便写的一张表,只是让其能循环插入50条记录

use tempdb
CHECKPOINT  --写入MDF中

exec sp_spaceused

--database_name    database_size    unallocated space
--tempdb        8.50 MB            6.62 MB
复制代码
复制代码

 

运行代码，我们发现，unallocated space 未用空间减小了，从6.75M减少至6.62M，说明临时表是占用了tempdb空间的，这点毋庸置疑。

 

我们接着看表变量又是如何？

复制代码
复制代码 
use tempdb
exec sp_spaceused

--database_name    database_size    unallocated space
--tempdb        8.50 MB            6.69 MB

use TestDB

declare @Table3 table (id int,  atypeid char(1024))
declare @count int = 50

INSERT INTO @Table3(id, atypeid)
SELECT TOP(@count) 1 as id, 'sss' as atypeid
FROM GraspFZDRPWrite001.dbo.BillType 

use tempdb
checkpoint

exec sp_spaceused
--database_name    database_size    unallocated space
--tempdb        8.50 MB            6.62 MB
复制代码
复制代码

 

unallocated space值再次变小，说明此操作存在占用tempdb的数据库空间。两者其实都存储在tempdb中，都占用tempdb的数据库空间。

 

2）对表变量记录的操作是否占用更少的LOG

我们首先看临时表插入

复制代码
复制代码 
if OBJECT_ID('tempdb..#T') is not null drop table #T

declare @b1 bigint, @b2 bigint

CREATE TABLE #T (s char(128))

SELECT @b1=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) 
declare @i int = 0
while @i<20000
BEGIN
  insert into #T select '临时表：原值'
  set @i=@i+1
END

use tempdb
checkpoint
select @b2=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) 
select @b2-@b1 as 日志增量   

--经测试，临时表日志增量  4851712
复制代码
复制代码

 

然后是表变量插入

复制代码
复制代码 
use TestDB

declare @b1 bigint, @b2 bigint
declare @V table (s char(128))
select @b1=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) 

declare @i int = 0
while @i<20000
begin
  insert into @V select '表变量：原值'
  set @i=@i+1
end
use tempdb
checkpoint
select @b2=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) 
select @b2-@b1 as 日志增量
--经测试，表变量日志增量5007360
复制代码
复制代码

 

两者日志记录相差不多，表变量还比临时表的日志写入更多！

 

3）Lock上的不同表现

复制代码
复制代码 
--临时表
if OBJECT_ID('tempdb..#T') is not null drop table #T

create table #T (s varchar(128))
insert into #T select '临时表：原值'

execute sp_lock @@spid  --查看当前用户进程的会话 所在的锁关系

BEGIN TRANSACTION
     update #T set s= '临时表：被更新'
     execute sp_lock @@spid  --发现增加了一个排他锁
ROLLBACK TRANSACTION

execute sp_lock @@spid  --排他锁被释放

GO

--表变量
declare @V table (s char(128))
insert into @V select '表变量：原值'

execute sp_lock @@spid

BEGIN TRANSACTION
     update @V set s='表变量：被更新'
     execute sp_lock @@spid
ROLLBACK TRANSACTION

execute sp_lock @@spid  --并没有在事务中加任何锁
复制代码
复制代码

 

临时表的更新会加锁，表变量更新不会

 

4）事务处理中的不同

复制代码
复制代码 
if OBJECT_ID('tempdb..#T') is not null drop table #T

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select '临时表：原值'
insert into @T select '表变量：原值'

BEGIN TRANSACTION
     update #T set s='临时表：被更新'
     update @T set s='表变量：被更新'
ROLLBACK TRANSACTION

select * from #T
select * from @T
复制代码
复制代码

 

结果发现，临时表得值被回滚，表变量并没有回滚。可以得出，表变量不受事务影响。

 

5）UDF中的不同

复制代码
复制代码 
--表变量
CREATE FUNCTION dbo.example1 ( ) RETURNS INT AS 
BEGIN 
    DECLARE @t1 TABLE (i INT) 
    INSERT @t1 VALUES(1) 
    INSERT @t1 VALUES(2) 
    UPDATE @t1 SET i = i + 5 
    DELETE @t1 WHERE i < 7 
 
    DECLARE @max INT 
    SELECT @max = MAX(i) FROM @t1 
    RETURN @max 
END 
GO;
--临时表
CREATE FUNCTION dbo.example2 ( ) RETURNS INT 
AS 
BEGIN 
    CREATE TABLE #t1 (i INT) 
    INSERT #t1 VALUES(1) 
    INSERT #t1 VALUES(2) 
    UPDATE #t1 SET i = i + 5 
    DELETE #t1 WHERE i < 7 
 
    DECLARE @max INT 
    SELECT @max = MAX(i) FROM #t1 
    RETURN @max 
END  
GO
--物理表
CREATE FUNCTION dbo.example3 ( ) RETURNS INT AS 
BEGIN 
    CREATE TABLE table1 ( id INT IDENTITY, name VARCHAR(32) ) 
    INSERT table1(name) VALUES('aaron') 
    RETURN SCOPE_IDENTITY() 
END
复制代码
复制代码

 

运行后，可以发现，函数内无法访问临时表，也无法创建实体表，同理也无法更新新增删除实体表的记录，但可以使用表变量来进行运算和操作

 

6) 性能对比

复制代码
复制代码 
if OBJECT_ID('tempdb..#T') is not null drop table #T
create table #T (s char(1024))
declare @i int = 0
while @i<100000
begin
  insert into #T select '临时表：原值'
  set @i=@i+1
END
SELECT * FROM #T  --3秒左右
--3秒
go

declare @V table (s char(1024))
declare @i int = 0
while @i<100000
begin
  insert into @V select '表变量：原值'
  set @i=@i+1
END
SELECT * FROM @V  --3秒左右，和临时表几乎一样
复制代码
复制代码

 

在插入性能上，两者基本一致

 

7）关联操作上，性能的不同表现

复制代码
复制代码 
--构造数据
dbcc dropcleanbuffers; --从缓冲池中删除所有清除缓冲区

use TestDB

if object_id('tempdb..#temp') is not null drop table #temp                    

SELECT IDENTITY(INT, 1, 1) as _rowid, a.ppt, a.GoodsId
INTO #temp 
FROM  GraspFZDRPWrite001.dbo.Goods a  --goods表是一个有记录16049的数据表

GO

--用临时表
SELECT * FROM #temp
WHERE  _rowid IN (SELECT max(_rowid) FROM #temp GROUP  BY Ppt) 
--很快 

--用表变量
DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT)
INSERT INTO @PDTEMP SELECT * FROM #temp

SELECT * FROM @PDTEMP   
WHERE  _rowid IN (SELECT max(_rowid) FROM  @PDTEMP GROUP  BY ppt) 

go
--相当慢，记录几乎出不来
复制代码
复制代码

 

为啥会出现以上的结果呢，我们跟踪执行计划可以发现，前者执行计划选择的哈希匹配，后者则是相当缓慢的嵌套循环。

 

分析原因，因为聚合操作会利用表的统计信息来聚合，表变量没有统计信息，系统默认只能选择嵌套循环，而这导致严重的慢查询的主要原因

 

我们强制查询使用hash join连接(哈希匹配)

复制代码
复制代码 
DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT)
INSERT INTO @PDTEMP SELECT * FROM #temp

SELECT * FROM @PDTEMP
WHERE  _rowid IN (SELECT max(_rowid) FROM  @PDTEMP GROUP  BY ppt) 
option(hash join)
复制代码
复制代码

 

这时速度跟用临时表一样，但不推荐这样使用，因为一旦这样强制使用，SQLSERVER的自动优化则不会起作用

 

8）把存储过程中返回的数据集插入到临时表 用于保存存储过程中返回的数据集

复制代码
复制代码 
CREATE TABLE #sp_who3 
( 
    SPID INT, 
    Status VARCHAR(32) NULL
)
go

create procedure pWho AS
  select 1 as spid, 'Tomas' as status
  union all
  select 1 as spid, 'Viviy' as status
go

insert #sp_who3 execute pWho

select * from #sp_who3
     
DECLARE @PDTEMP TABLE (SPID INT, Status VARCHAR(32) NULL)
INSERT @PDTEMP EXEC pWho 
SELECT * FROM @PDTEMP

GO
复制代码
复制代码

 

两者都可以正常使用，但是有一点必须注意，对于2008以前的版本，表变量是不支持这样操作的

 

9）是否可以动态的生成列

--临时表
SELECT * INTO #TEMP_objects FROM sys.objects; --成功执行

--表变量
SELECT * INTO @PDTEMP FROM sys.objects;   --报错

 

表变量不能生成动态列，因为表变量一旦创建，他的架构就是固定的，而临时表可以更改架构甚至是索引

 

10）生命期是否相同

复制代码
复制代码 
if OBJECT_ID('tempdb..#temp_foo') is not null drop table #temp_foo

--临时表
DECLARE @sql VARCHAR(8000) 
SET @sql = 'Create TABLE #temp_foo (a INT,b INT,c INT)   Insert into #temp_foo values(1,1,1) ' 
EXEC(@sql) 
INSERT #temp_foo SELECT 1,2,3  
SELECT * FROM #temp_foo
GO

--表变量
DECLARE @sql VARCHAR(8000) 
SET @sql = 'DECLARE @foo TABLE(a INT,b INT,c INT) Insert into @foo values(1,1,1)' 
EXEC(@sql) 
INSERT @foo SELECT 1,2,3 
Go;
复制代码
复制代码

 

同时存在两个不同的批处理，外面的批处理要调用里面的批处理声明的表，临时表和表变量均不能使用

 

复制代码
复制代码 
--临时表
DECLARE @sql VARCHAR(8000) 
Create TABLE #temp_foo (a INT,b INT,c INT)   
SET @sql = 'Insert into #temp_foo values(1,1,1)' 
EXEC(@sql) 
INSERT #temp_foo SELECT 1,2,3 
SELECT * FROM #temp_foo
GO 
 --可以执行

--表变量
DECLARE @sql VARCHAR(8000) 
DECLARE @foo TABLE(a INT,b INT,c INT)
SET @sql = 'Insert into @foo values(1,1,1)'  
EXEC(@sql) 
INSERT @foo SELECT 1,2,3 
SELECT * FROM @foo
Go
--执行到EXEC(@SQL)报错，找不到@foo
复制代码
复制代码

 

临时表在跨批处理中，里面的批处理可以调用到外面声明的表，表变量却不行（实体表只要是声明在前，都可以被调用）

 

 

总结： 无表关联操作，只作为中间集进行数据处理，建议用表变量；有表关联，且不能确定数据量大小的情况下，建议用临时表。

本文转自左正博客园博客，原文链接：http://www.cnblogs.com/soundcode/archive/2012/09/18/2690020.html ，如需转载请自行联系原作者
文章标签：
日志服务
SQL
监控
Go
存储
数据库
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
吞吞吐吐的
目录
相关文章
我是廖志伟
|
6月前
|
存储 数据库 索引
【面试官挖坑】聚集索引会隐式的自动为表添加一个主键索引那你是不是就可以不设置主键索引了？
【面试官挖坑】聚集索引会隐式的自动为表添加一个主键索引那你是不是就可以不设置主键索引了？
我是廖志伟
21 0
从未止步..
|
6月前
|
SQL 存储 关系型数据库
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~1
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~
从未止步..
64 0
从未止步..
|
6月前
|
存储 SQL 关系型数据库
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~2
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~2
从未止步..
39 0
从未止步..
|
6月前
|
SQL 存储 关系型数据库
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~3
MySQL基础下篇[表的创建/约束的使用/事务和范式以及索引的使用]~
从未止步..
42 0
虫无涯
|
SQL 关系型数据库 MySQL
MySql基础-笔记11-临时表、复制表、元数据、序列使用
MySql基础-笔记11-临时表、复制表、元数据、序列使用
虫无涯
129 0
MySql基础-笔记11-临时表、复制表、元数据、序列使用
猫头虎
|
SQL 关系型数据库 MySQL
MySQL索引详解（优缺点，何时需要/不需要创建索引，索引及sql语句的优化）
目录 MySQL索引详解（优缺点，何时需要/不需要创建索引，索引及sql语句的优化） 一、什么是索引？ 二、索引的作用？ 三、优点： 四、缺点 五、什么时候需要创建索引 六、什么时候不需要创建索引 七、索引的分类： 八、索引和sql语句的优化 1、前导模糊查询不能使用索引， 2、Union、in、or可以命中索引，建议使用in 3、负条件查询不能使用索引，可以优化为in查询， 4、联合索引最左前缀原则，又叫最左侧查询， 5、建立联合查询时，区分度最高的字段在最左边 6、如果建立了（a，b）联合索引，就不必再单独建立a索引。 7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置
猫头虎
253 0
MySQL索引详解（优缺点，何时需要/不需要创建索引，索引及sql语句的优化）
游客r426lz522voxi
|
SQL 测试技术
临时表在SQL优化中的作用
今天我们来讲讲临时表的优化技巧 临时表，顾名思义就只是临时使用的一张表，一种是本地临时表，只能在当前查询页面使用，新开查询是不能使用它的，一种是全局临时表，不管开多少查询页面均可使用。
游客r426lz522voxi
155 0
临时表在SQL优化中的作用
浩秦
|
BI
西软报表处理语句相关
select s.staffcd, s.staffname, s.staffpassword, o.objectcd as deptcd, o.companyname as deptdrptfrom castaff s left outer join caorg o on s.
浩秦
846 0
汪凡640
|
SQL 关系型数据库 MySQL
(解释文)My SQL中主键为0和主键自排约束的关系
　　上一篇我们说了关于自排如果主键是0的问题，在这里我搞清楚了原因，导致这种情况是因为在SQL中对自排设置了初始值： 　　 　　从这里可以看到这两个变量一个是自增的初始值，一个是增量，这里都是1，所以在设置自增的时候会把那个字段原来存在的所有0变成从1开始的步长为1的等差数列。
汪凡640
979 0
范大脚脚
|
SQL 存储 数据库
【译】表变量和临时表的比较
范大脚脚
982 0

热门文章

最新文章

  • 1
    随机生成UserAgent的python库（fake-useragent库）
  • 2
    使用zxing识别一幅包含多个二维码的图片
  • 3
    阿里云的SLB，植入cookie和重写cookie有什么区别？
  • 4
    基于Flink的实时日志分析系统实践
  • 5
    如何在服务器上跑python程序
  • 6
    电池技术多年没有较大发展，成为移动设备最大制约
  • 7
    Spread for WinRT 7新功能使用指南
  • 8
    codeforces Soldier and Number Game（dp+素数筛选）
  • 9
    九度oj-1001-Java
  • 10
    Android:随笔——对页面的View进行截图
  • 1
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势2
    9
  • 2
    分解商业周期时间序列：线性滤波器、HP滤波器、Baxter滤波器、Beveridge Nelson分解等去趋势法1
    9
  • 3
    灰色关联分析(Grey Relation Analysis,GRA)中国经济社会发展指标
    9
  • 4
    极值分析：分块极大值BLOCK-MAXIMA、阈值超额法、广义帕累托分布GPD拟合降雨数据时间序列
    9
  • 5
    GARCH-DCC模型和DCC（MVT）建模估计
    8
  • 6
    ARIMA、ARIMAX、 动态回归和OLS 回归预测多元时间序列
    10
  • 7
    R语言预测期货波动率的实现：ARCH与HAR-RV与GARCH，ARFIMA模型比较
    10
  • 8
    spss modeler用决策树神经网络预测ST的股票
    9
  • 9
    K-means和层次聚类分析癌细胞系微阵列数据和树状图可视化比较
    8
  • 10
    matlab中使用VMD(变分模态分解)对信号去噪
    8

    • 相关电子书

    更多
  • RowKey与索引设计：技巧与案例分析
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 使用SQL语句实现数据插入、修改和删除操作
  • SQL进阶之约束、索引
  • SQL进阶之子句、关键字和操作符
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4