临时表与表变量的深入探究
临时表与表变量的深入探究
临时表或表变量我们一般用来充当中间结果集，很多时候都在用，但真正了解他们之间的区别的人还是很少的，网上流传的说法也不甚统一，所以今天我就做一个实验，让我们看看临时表和表变量的区别，以及他们各自的用途。
执行以下语句，对测试环境做准备
DBCC DROPCLEANBUFFERS --从缓冲池中删除所有清除缓冲区
DBCC FREEPROCCACHE --清除计划缓存
CHECKPOINT --写入MDF中
1） 关于存储
表变量在内存中，是否真的不写磁盘，不会造成任何IO开销？
www.zzzyk.com
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空间的，这点毋庸置疑。
www.zzzyk.com
我们接着看表变量又是如何？
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
www.zzzyk.com
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
www.zzzyk.com
然后是表变量插入
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
www.zzzyk.com
两者日志记录相差不多，表变量还比临时表的日志写入更多！
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 --排他锁被释放
www.zzzyk.com
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
www.zzzyk.com
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 www.zzzyk.com
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
上一个：MongoDB学习笔记(聚合)
下一个：数据处理的三种方式
