在临时表
create table #T (…)
和表变量
declare @T table (…)
之间主要有
3
个理论上的不同。
第一个
不同使事务日志不会记录表变量。因此，它们脱离了事务机制的范围，从下面的例子可显而易见：
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T
s
---------------
old value #
s
---------------
new value @
在声明临时表
#T
和表变量
@T
之后，给它们分配一个相同的值为
old value
字符串。然后，开始一个事务去更新它们。此时，它们都将有新的相同的值
new value
字符串。但当事务回滚时，正如你所看到的，表变量
@T
保留了这个新值而没有返回
old value
字符串。这是因为即使表变量在事务内被更新了，它本身不是事务的一部分。
第二个
主要的不同是任何一个使用临时表的存储过程都不会被预编译，然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著，因为对它来说重新编译代价太高。
最后
，表变量仅存在于那些变量能存在的相同范围内。和临时表相反，它们在内部存储过程和
exec
（
string
）语句里是不可见的。它们也不能在
insert/exec
语句里使用。
性能比较
首先，准备一个有
100
万记录的测试表：
create table NUM (n int primary key, s varchar(128))
GO
set nocount on
declare @n int
set @n=1000000
while @n>0 begin
insert into NUM
select @n,'Value: '+convert(varchar,@n)
set @n=@n-1
end
GO
准备测试存储过程
T1
：
create procedure T1
@total int
as
create table #T (n int, s varchar(128))
insert into #T select n,s from NUM
where n%100>0 and n<=@total
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
not exists(select * from #T
where #T.n=NUM.n)
GO
使用参数从
10
，
100
，
1000
，
10000
，
100000
到
1000000
不等来调用，它复制给定数量的记录到临时表（一些另外，它跳过那些能被
100
整除的数值），然后找到缺失记录的最大值。当然，记录越多，执行的时间就越长：
为了测量正好的执行时间，使用下面的代码：
declare @t1 datetime, @n int
set @t1=getdate()
set @n=
100 – (**)
while @n>0 begin
exec T1
1000 – (*)
set @n=@n-1 end
select datediff(ms,@t1,getdate())
GO
（
*
）表示程序里边的参数从
10
到
1000000
不等。
（
**
）表示如果执行时间太短，就重复相同的循环
10
到
100
次不等。
多次运行代码以获得执行的结果。
该结果在下面的表
1
里能找到。
下面试着给临时表添加一个主键来提升存储过程的性能：
create procedure
T2
@total int
as
create table #T (n int
primary key
, s varchar(128))
insert into #T select n,s from NUM
where n%100>0 and n<=@total
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
not exists(select * from #T
where #T.n=NUM.n)
GO
然后，创建第三个。此时有聚集索引，它会工作得更好。但是是在插入数据到临时表之后创建的索引——通常，这样会更好：
create procedure
T3
@total int
as
create table #T (n int, s varchar(128))
insert into #T select n,s from NUM
where n%100>0 and n<=@total
create clustered index Tind on #T (n)
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
not exists(select * from #T
where #T.n=NUM.n)
GO
令人惊奇！大数据量花费的时间很长；仅仅添加
10
条记录就花费了
13
毫秒。这个问题在于创建索引语句强迫
SQLServer
去重新编译存储过程，显著的降低了执行效率。
现在试着使用表变量来完成相同的事情：
create procedure
V1
@total int
as
declare @V table (n int, s varchar(128))
insert into
@V
select n,s from NUM
where n%100>0 and n<=@total
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
not exists(select * from
@V V
where V.n=NUM.n)
GO
使我们惊奇的是，该版本不是明显的比用临时表的快。这是由于在存储过程开头创建表
#T
语句时进行了特别优化的缘故。对整个范围内的值，
V1
和
T1
工作得一样好。
下面试试有主键的情形：
create procedure
V2
@total int
as
declare @V table (n int
primary key
, s varchar(128))
insert into @V select n,s from NUM
where n%100>0 and n<=@total
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
not exists(select * from @V V
where V.n=NUM.n)
GO
这个结果很快，但
T2
超过了该版本。
|
Records |
T1 |
T2 |
T3 |
V1 |
V2 |
|
10 |
0.7 |
1 |
13.5 |
0.6 |
0.8 |
|
100 |
1.2 |
1.7 |
14.2 |
1.2 |
1.3 |
|
1000 |
7.1 |
5.5 |
27 |
7 |
5.3 |
|
10000 |
72 |
57 |
82 |
71 |
48 |
|
100000 |
883 |
480 |
580 |
840 |
510 |
|
1000000 |
45056 |
6090 |
15220 |
20240 |
12010 |
表
1
：使用
SQLServer2000
，时间单位毫秒
但真正使我们震惊的是在
SQLServer2005
上的情形：
|
N |
T1 |
T2 |
T3 |
V1 |
V2 |
|
10 |
0.5 |
0.5 |
5.3 |
0.2 |
0.2 |
|
100 |
2 |
1.2 |
6.4 |
61.8 |
2.5 |
|
1000 |
9.3 |
8.5 |
13.5 |
168 |
140 |
|
10000 |
67.4 |
79.2 |
71.3 |
17133 |
13910 |
|
100000 |
700 |
794 |
659 |
Too long! |
Too long! |
|
1000000 |
10556 |
8673 |
6440 |
Too long! |
Too long! |
表
2
：使用
SQLServer2005
（时间单位毫秒）
有时，
SQL2005
比
SQL2000
快（上面标记为绿色的部分）。但大多数情况下，特别是在数据量巨大时，存储过程使用表变量花费了更长的时间（红色部分）。在
4
种情形下，我甚至放弃了等待。
结论
- 在什么时候和什么地方使用临时表或表变量没有一个普遍的规则。试着都测试测试它们。
- 在你的测试里，少量的记录和大量的数据集都要进行测试。
- 当在你的存储过程里使用了复杂的逻辑的时候要小心迁移到
SQL2005
。相同的代码在
SQLServer2005
上可能运行要慢
10
到
100
倍。