SQL 临时表与临时变量表

 

在SQLServer的性能调优中，有一个不可比拟的问题：那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。如何确定什么时候用临时表，什么时候用表变量呢？临时表和表变量都有特定的适用环境。 　　表变量 　　变量都以@或@@为前缀，表变量是变量的一种，另外一种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者日期型DateTime)。以@前缀的表变量是本地的，因此只有在当前用户会话中才可以访问，而@@前缀的表变量是全局的，通常都是系统变量，比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量，因此它只能在一个Batch中生存，也就是我们所说的边界，超出了这个边界，表变量也就消亡了。 　　存放在内存中，正是因为这一点所有用户访问表变量的时候SQLServer是不需要生成志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。 　　另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事，而有些时候却会造成一些麻烦。 　　临时表 　　临时对象都以#或##为前缀，临时表是临时对象的一种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。以#前缀的临时表为本地的，因此只有在当前用户会话中才可以访问，而##前缀的临时表是全局的，因此所有用户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然用户在会话中可以通过DROPTABLE命令提前销毁临时表。 　　我们前面说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。 　　跟表变量另外一个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQLServer在处理访问临时表的语句时需要考虑执行计划优化的问题。 　　简单地总结，我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。 　　一般对于大的数据集我们推荐使用临时表，同时创建索引，或者通过SQLServer的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据，当然临时表就是唯一的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。 　　作者：DrillChina     什么情况下使用表变量？ 什么情况下临时表？ --------------------------------------------------------------- 表变量只存放在内存中，临时表需要写磁盘。所以一般情况下，用表变量会快些。 用法不太一样，有些时候用表变量更方便。 具体的再看SQL 2000的帮助。试用下就知道了。 --------------------------------------------------------------- 个人观点: 1、表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。 2、临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用TEMPDB的空间，这样主 要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间，然后使用临时表。 --------------------------------------------------------------- 肤浅理解：        表变量:需要事先知道表结构        普通临时表：只在当前会话中可用与表变量相同 into一下就可以了,方便        全局临时表：可在多个会话中使用存在于temp中需显示的drop --------------------------------------------------------------- 要从表变量的作用域，支持不支持的操作，机器内存大小等几方面考虑。 如： .表变量相当于ADO的RECORDSET，速度比临时表快得多。 表变量不能用在下列语句中： INSERT INTO table_variable EXEC 存储过程。 SELECT select_list INTO table_variable 语句。 在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。 但临时表支持。 .表变量速度比临时表快得多（如果内存足够） 如果数据量不大： 微软 BOOK ON LINE 内说:尽可能使用表变量而不使用临时表 --------------------------------------------------------------- 贴出来看看： 尽可能使用表变量而不使用临时表。table 变量有以下优点：    table 变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。    在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是，table 不能用在下列语 句中： INSERT INTO table_variable EXEC 存储过程。 SELECT select_list INTO table_variable 语句。 在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。 在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。 涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。    不支持在表变量之间进行赋值操作。另外，由于表变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。 --------------------------------------------------------------- 个人认为： 1.表变量在内存中，临时表存放在硬盘上； 2.用临时表要考虑锁不锁表的问题； 3.数据量太大应该用临时表。 临时表要及时删除 要不然会使系统崩溃