区别一:

<PRE lang=sql>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 @</PRE>

这行代码一目了然,临时表能起到事务回滚的作用,但是table变量不可以.作者解释是table变量不在事务作用范围之内.所以当table变量即使遇到回滚命令,但是也不会真的执行回滚.

区别二:
任何含有临时表的存储过程是不能被预编译的.这在一个很长的存储过程中,优势会更加凸显.

区别三:
table变量作为变量只能在同范围内存在,不可能跨范围.还有就是table变量在内置的存储过程中或者exec(string)语句中是不可见的还有就是不能被用于INSERT/EXEC statements.

下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.
创建一个测试表:

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

这个存储过程的参数@Total给定的越大,那么执行的时间越长.

为精确测量存储过程执行时间,我使用了以下代码:

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

(*) 是存储过程参数.

现在我们给这个存储过程来第一次提速:个它加个主键

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

很惊讶速度已经提高了很多!

那好了,咱们再来测试一下table变量的速度吧

CREARE 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

然后再来创建一个有主键的:

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
      WHEREre V.n=NUM.n)
GO<

然后咱们来看看我测试的结果吧!

But the real shock is when you try the same on SQL Server 2005:

发现对比在某些情况下sql2000的速度要比2005的要快上很多!

结论:
没有通用的规则指导你什么时候用临时表什么时候用table变量.
将复杂逻辑的存储过程移植到sql2005的时候,要格外小心!他可能比2000的效率要慢上好几十倍的!
在你的实际测试中,请测试两个极端:销量数据和超大量数据.

此文章翻译自codeproject:http://www.codeproject.com/cs/database/SQP_performance.asp