表变量

 表变量是变量，所以

           不可创建索引

           批处理结束后会自动删除

                   (不需要在存储过程结束的时候释放（DROP），导致存储过程编译更少)

           不需要考虑锁机制

 

临时表是表，所以

           可以创建索引

           需要显式删除

 

 

表变量：
说白了和表没什么区别，只不过是以变量的形式存在，你像访问普通的表一样去访问，去做增删改查的操作。最重要的是为什么有了临时表，还要整个表变量呢，存在即合理，表变量确实有其优点，例如它不需要在存储过程结束的时候释放（DROP），导致存储过程编译更少等等 

使用表变量应该比临时表要高效，但是局限也比较多：只能有一个主键。如果记录多，效率反而比临时表低 

表变量都以@或@@为前缀,表变量是变量的一种，
以@前缀的表变量是本地的因此只有在当前用户会话中才可以访问，
而@@前缀的表变量是全局的通常都是系统变量。比如说@@error代表最近的一个T-SQL语句的报错号。

当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界超出了这个边界表变量也就消亡了。表变量存放在内存中正是因为这一点，所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制对于非常繁忙的系统来说避免锁的使用可以减少一部分系统负载。
表变量另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事而有些时候却会造成一些麻烦。


表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 另一个不同的地方是在表连接中使用表变量时,要为此表变量指定别名.如:

USE AdventureWorksDWGODECLARE @DimCustomer_test TABLE([CustomerKey] [int] ,[FirstName] [nvarchar](50) , [MiddleName] [nvarchar](50) , [LastName] [nvarchar](50) ,)INSERT @DimCustomer_test( [CustomerKey],[FirstName],[MiddleName],[LastName] ) SELECT   [CustomerKey],[FirstName] ,[MiddleName],[LastName]  FROM DimCustomerSELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)    FROM @DimCustomer_test   INNER JOIN FactInternetSales  ON    @DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey    Group BY CustomerKey

 

Result:
Server: Msg 137, Level 15, State 2, Line 32
Must declare the variable

如果我们对上面的查询进行更改,对查询使用别名(并且找开IO):

INSERT @DimCustomer_test( [CustomerKey],[FirstName],[MiddleName],[LastName] ) SELECT   [CustomerKey],[FirstName] ,[MiddleName],[LastName]  FROM DimCustomer    SELECT t.CustomerKey,f.OrderQuantityFROM @DimCustomer_test as t INNER JOIN FactInternetSales   f ONt.CustomerKey = f.CustomerKey

 

临时表和表变量的作用域范围：
       表变量的作用域范围是所在的批处理，而临时表的作用域范围是整个会话。还有表变量不会产生统计信息，这有两个主要后果：第一是查询优化器对表变量行数的估计为固定值而不管表变量中的数据是什么，第二添加或删除数据并不会改变估计的值。
不能为表变量创建索引但是可以为临时表创建索引。

表变量在批处理结束时自动被系统删除,所以你不必要像使用临时表一样显示的对它进行删除.
表变量和临时表针对我们使用人员来说并没有什么不同,但是在存储方面来说,他们是不同的,表变量存储在内存中.所以在性能上和临时表相比会更好些! 
3个理论上的不同。
第一个不同是事务日志不会记录表变量。因此它们脱离了事务机制的范围。
第二个主要的不同是任何一个使用临时表的存储过程都不会被预编译,然而使用表变量的存储过程的执行计划可以预先静态的编译。预编译一个脚本的主要好处在于加快了执行的速度。这个好处对于长的存储过程更加显著因为对它来说重新编译代价太高.
最后表变量仅存在于那些变量能存在的相同范围内。和临时表相反它们在内部存储过程和execstring语句里是不可见的。它们也不能在insert/exec语句里使用


////////////////////////////////////////////////////
可不可以把表变量作为存储过程/函数的参数用 ？

1、可以用实体表的表名做参数，然后存储过程里面 exec 动态拼接。 

create proc proc_table@tablename varchar(30)asdeclare @sql varchar(max) set @sql='select * from '+@tablename+''print @sqlexec(@sql)

 
2、
你可以这样，先定义一个表类型，然后在定义表变量的时候，直接使用该类型即可，例如：
创建表类型：

create type dbo.OrderTotalsByYear as table(    orderyear int not null primary key,    qty int not null)

 
定义一个表变量直接使用表类型：

declare @MyOrderTotalsByYear as dbo.OrderTotalsByYearinsert into @MyOrderTotalsByYear (orderyear,qty)select YEAR(a.orderdate) as orderyear,SUM(b.qty) as qty from sales.Orders a inner join sales.OrderDetails b on a.orderid=b.orderid group by YEAR(a.orderdate)select * from @MyOrderTotalsByYear

 

可以将@MyOrderTotalsByYear作为存储过程的参数。但是函数我没试过，另外只限于SQL2008及以上版本。