临时表与表变量的深入探究

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

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

DBCC DROPCLEANBUFFERS --从缓冲池中删除所有清除缓冲区

DBCC FREEPROCCACHE --清除计划缓存

CHECKPOINT --写入MDF中

1） 关于存储

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

www.2cto.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 Gr aspFZDRPWrite001.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.2cto.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.2cto.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.2cto.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.2cto.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.2cto.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