SQL Server中的临时表和表变量

转载 2015年07月09日 15:08:25

在SQL Server的性能调优中,有一个不可比面的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。记得在给一家国内首屈一指的海运公司作SQL Server应用性能评估和调优的时候就看到过大量的临时数据集处理需求,而他们的开发人员就无法确定什么时候用临时表,什么时候用表变量,因此他们就简单的使用了临时表。实际上临时表和表变量都有特定的适用环境。

先卖弄一些基础的知识:

表变量

变量都以@或@@为前缀,表变量是变量的一种,另外一种变量被称为标量(可以理解为标准变量,就是标准数据类型的变量,例如整型int或者日期型DateTime)。以@前缀的表变量是本地的,因此只有在当前用户会话中才可以访问,而@@前缀的表变量是全局的,通常都是系统变量,比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量,因此它只能在一个Batch中生存,也就是我们所说的边界,超出了这个边界,表变量也就消亡了。

表变量存放在内存中,正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题,因此也不需要锁机制,对于非常繁忙的系统来说,避免锁的使用可以减少一部分系统负载。

表变量另外还有一个限制就是不能创建索引,当然也不存在统计数据的问题,因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了),这一特性有的时候是件好事,而有些时候却会造成一些麻烦。

临时表

临时对象都以#或##为前缀,临时表是临时对象的一种,还有例如临时存储过程、临时函数之类的临时对象,临时对象都存储在tempdb中。以#前缀的临时表为本地的,因此只有在当前用户会话中才可以访问,而##前缀的临时表是全局的,因此所有用户会话都可以访问。临时表以会话为边界,只要创建临时表的会话没有结束,临时表就会持续存在,当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。

我们前面说过临时表存储在tempdb中,因此临时表的访问是有可能造成物理IO的,当然在修改时也需要生成日志来确保一致性,同时锁机制也是不可缺少的。

跟表变量另外一个显著去别就是临时表可以创建索引,也可以定义统计数据,因此SQL Server在处理访问临时表的语句时需要考虑执行计划优化的问题。

表变量 vs. 临时表

 

结论

综上所述,大家会发现临时表和表变量在底层处理机制上是有很多差别的。

简单地总结,我们对于较小的临时计算用数据集推荐使用表变量。如果数据集比较大,如果在代码中用于临时计算,同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化,比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等),也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力,如果代码的运行实例很多,就要特别注意内存变量对内存的消耗。

一般对于大的数据集我们推荐使用临时表,同时创建索引,或者通过SQL Server的统计数据(Statisitcs)自动创建和维护功能来提供访问SQL语句的优化。如果需要在多个用户会话间交换数据,当然临时表就是唯一的选择了。需要提及的是,由于临时表存放在tempdb中,因此要注意tempdb的调优。

SQL Server 表变量和临时表系列之概念篇

问题引入 “菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了...
  • lishimin1012
  • lishimin1012
  • 2017年01月06日 14:37
  • 544

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server ...
  • lovehongyun
  • lovehongyun
  • 2008年01月09日 09:27
  • 10990

sqlserver 中临时表、临时变量和with as关键词创建“临时表”的区别

SQL数据库中数据处理时,有时候需要建立临时表,将查询后的结果集放到临时表中,然后在针对这个数据进行操作。   创建“临时表”(逻辑上的临时表,可能不一定是数据库的)的方法有一下几种:  ...
  • miqi770
  • miqi770
  • 2016年05月26日 15:39
  • 3952

SQL Server临时表和表变量

文章主要描述的是SQL Server临时表和表变量在实际操作中的区别介绍,我们在数据库中实际操作中,对于表的使用的时候,一般都会使用两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们...
  • andycode
  • andycode
  • 2011年07月25日 11:09
  • 407

SQL server 临时表和表变量

临时表临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志,...
  • misol
  • misol
  • 2010年12月15日 09:25
  • 616

SQL SERVER临时表的使用&表变量的使用

临时表: 创建临时表可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个...
  • S630730701
  • S630730701
  • 2016年08月03日 16:57
  • 2448

SQL Server中的临时表和表变量

在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。   在SQL Server的性能...
  • socoldinxian
  • socoldinxian
  • 2013年08月30日 17:36
  • 516

SQL Server中的临时表和表变量

SQL Server中的临时表和表变量 http://www.weaseek.com  2008-07-08 11:49:33  来源:IT专家网 在SQL Server的性能调优中,有一个不可比拟的...
  • wuzhong369
  • wuzhong369
  • 2009年02月16日 14:19
  • 324

sql临时表:SQL Server中的临时表和表变量

在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。  在SQL Server的性能调优中,有一个不...
  • dingchenglong00
  • dingchenglong00
  • 2010年03月04日 08:55
  • 175

利用临时表对查询数据重新排序

先创建临时表,然后把查询结果存入临时表,最后从临时表查询数据并按需要重新排序。...
  • gaofeng2000
  • gaofeng2000
  • 2010年10月29日 23:27
  • 1324
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server中的临时表和表变量
举报原因:
原因补充:

(最多只允许输入30个字)