临时表vs.表变量以及它们对SQLServer性能的影响

 问题 1：为什么在已经有了临时表的情况下还要引入表变量？

解答 1：与临时表相比，表变量具有下列优点： • 如 SQL Server 联机丛书“表”(Table) 一文中所述，表变量（如局部变量）具有明确定义的范围，在该范围结束时会自动清除这些表变量。 • 与临时表相比，表变量导致存储过程的重新编译更少。 • 涉及表变量的事务仅维持表变量上更新的持续时间。因此，使用表变量时，需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分，所以事务回滚并不影响它们。 问题 2：如果说使用表变量比使用临时表导致存储过程的重新编译更少，这意味着什么？

解答 2：下面的文章讨论了重新编译存储过程的一些原因：

243586 (http://support.microsoft.com/kb/243586/) 存储过程重新编译的疑难解答

“由于某些临时表操作引起的重新编译”一节还列出了为避免一些问题（例如使用临时表导致重新编译）而需要满足的一些要求。这些限制不适用于表变量。

表 变量完全独立于创建这些表变量的批，因此，当执行 CREATE 或 ALTER 语句时，不会发生“重新解析”，而在使用临时表时可能会发生“重新解析”。临时表需要此“重新解析”，以便从嵌套存储过程引用该表。表变量完全避免了此问 题，因此存储过程可以使用已编译的计划，从而节省了处理存储过程的资源。

问题 3：表变量有哪些缺陷？

解答 3：与临时表相比，它存在下列缺陷： • 在表变量上不能创建非聚集索引（为 PRIMARY 或 UNIQUE 约束创建的系统索引除外）。与具有非聚集索引的临时表相比，这可能会影响查询性能。 • 表变量不像临时表那样可以维护统计信息。在表变量上，不能通过自动创建或使用 CREATE STATISTICS 语句来创建统计信息。因此，在大表上进行复杂查询时，缺少统计信息可能会妨碍优化器确定查询的最佳计划，从而影响该查询的性能。 • 在初始 DECLARE 语句后不能更改表定义。 • 表变量不能在 INSERT EXEC 或 SELECT INTO 语句中使用。 • 表类型声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。 • 如果表变量是在 EXEC 语句或 sp_executesql 存储过程外创建的，则不能使用 EXEC 语句或 sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用，因此 EXEC 语句和 sp_executesql 存储过程将在表变量的作用域之外。但是，您可以在 EXEC 语句或 sp_executesql 存储过程内创建表变量并执行所有处理，因为这样表变量本地作用域将位于 EXEC 语句或 sp_executesql 存储过程中。 问题 4：与临时表或永久表相比，表变量的仅存在于内存中的结构保证了更好的性能，是否因为它们是在驻留在物理磁盘上的数据库中维护的？

解答 4：表变量不是仅存在于内存中的结构。由于表变量可能保留的数据较多，内存中容纳不下，因此它必须在磁盘上有一个位置来存储数据。与临时表类似，表变量是在 tempdb 数据库中创建的。如果有足够的内存，则表变量和临时表都在内存（数据缓存）中创建和处理。

问题 5：必须使用表变量来代替临时表吗？

解答 5：答案取决于以下三个因素： • 插入到表中的行数。 • 从中保存查询的重新编译的次数。 • 查询类型及其对性能的指数和统计信息的依赖性。 在某些情况下，可将一个具有临时表的存储过程拆分为多个较小的存储过程，以便在较小的单元上进行重新编译。

通常情况下，应尽量使用表变量，除非数据量非常大并且需要重复使用表。在这种情况下，可以在临时表上创建索引以提高查询性能。但是，各种方案可能互不相同。Microsoft 建议您做一个测试，来验证表变量对于特定的查询或存储过程是否比临时表更有效。


---------------------------------------------
以下来自WindBI
---------------------------------------------

在临时表
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倍。