|
在临时表 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超过了该版本。
表1：使用SQLServer2000，时间单位毫秒 但真正使我们震惊的是在SQLServer2005上的情形：
有时，SQL2005比SQL2000快（上面标记为绿色的部分）。但大多数情况下，特别是在数据量巨大时，存储过程使用表变量花费了更长的时间（红色部分）。在4种情形下，我甚至放弃了等待。 结论
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
拓狼 |
表变量从不产生统计信息，所以优化器总是假定它只有一条记录，而不管它们实际上有多少条。 临时表可以产生统计信息，和永久表一样存储在柱状图里，以备优化器使用。 －－－见于《Dissecting SQL Server Execution Plans》的17页与18页之间 |
|
|
sp里使用临时表时，如果表行数会因为参数的不同有很大变化，会有重编译的问题 |
|
|
每种临时对像都有自己的优点，表变量的优势是它所涉及的重新编译次数较少。缺点拓狼提到了，因为缺少分布统计信息，当优化器需要用直方图确定选择性时，可能会得到低效的查询计划。特别是当数据量大的时候尤为严重。 感觉数据量少的时候可以用表变量，而重要的查询用临时表。但当你大量使用临时表时，可能你得多多注意你的tempdb，它可能会成为你系统中的瓶颈。 以上观点参考自《Inside Microsoft SQL Server 2005 T-SQL Programming》 |
