Table变量和临时表区别

[[wiki]wiki[/wiki]][/wiki]区别一:
复制[wiki]SQL[/wiki]代码保存代码CREATE table #T (s varchar(128))
DECLARE @T table (s varchar(128))
INSERT into #T select 'old val[wiki]UE[/wiki] #'
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 @
这行代码一目了然,临时表能起到事务回滚的作用,但是table变量不可以.作者解释是table变量不在事务作用范围之内.所以当table变量即使遇到回滚命令,但是也不会真的执行回滚.


区别二:

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


区别三:

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

下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.
创建一个[wiki]测试[/wiki]表:
复制SQL代码保存代码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
复制SQL代码保存代码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给定的越大,那么执行礫wiki]氖[/wiki]奔湓匠