临时表和表变量

时间:2022-07-16 09:02:27

概念:

  临时表:以“#”号开头,
  全局临时表:以“##”号开头。

  表变量:即变量,用declare定义的变量。 如:Declare   @Rec     table     (ID   Int   Identity(1,1),     Name   varchar(40))    

详细概念如下:
    

 表变量
  变量都以@或@@为前缀,表变量是变量的一种,另外一种变量被称为标量(可以理解为标准变量,就是标准数据类型的变量,例如整型int或者日期型DateTime)。以@前缀的表变量是本地的,因此只有在当前用户会话中才可以访问,而@@前缀的表变量是全局的,通常都是系统变量,比如说@@error代表最近的一个T-SQL语句的报错号。当然因为表变量首先是个变量,因此它只能在一个Batch中生存,也就是我们所说的边界,超出了这个边界,表变量也就消亡了。
  表变量存放在内存中,正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题,因此也不需要锁机制,对于非常繁忙的系统来说,避免锁的使用可以减少一部分系统负载。
  表变量另外还有一个限制就是不能创建索引,当然也不存在统计数据的问题,因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了),这一特性有的时候是件好事,而有些时候却会造成一些麻烦。

  临时表
  临时对象都以#或##为前缀,临时表是临时对象的一种,还有例如临时存储过程、临时函数之类的临时对象,临时对象都存储在tempdb中。以#前缀的临时表为本地的,因此只有在当前用户会话中才可以访问,而##前缀的临时表是全局的,因此所有用户会话都可以访问。临时表以会话为边界,只要创建临时表的会话没有结束,临时表就会持续存在,当然用户在会话中可以通过DROP TABLE命令提前销毁临时表。
  我们前面说过临时表存储在tempdb中,因此临时表的访问是有可能造成物理IO的,当然在修改时也需要生成日志来确保一致性,同时锁机制也是不可缺少的。
  跟表变量另外一个显著去别就是临时表可以创建索引,也可以定义统计数据,因此SQL Server在处理访问临时表的语句时需要考虑执行计划优化的问题。

   表变量 vs. 临时表

  表变量 临时表
数据集的存储位置 内存(不考虑被换到页面文件这种情况) 磁盘(不考虑访问后被缓存到内存中)
是否需要日志
是否可以创建索引
是否可以使用统计数据
是否可以在多会话中访问
是否需要锁机制

两者特点:
  

1. 为什么要使用表变量
  表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
b.在存储过程中使用表变量会减少存储过程重新编译的发生;
c.表变量需要更少的锁请求和日志资源;
d.可以在表变量上使用UDF,UDDT,XML。

2.表变量的限制
  与临时表相比,表变量存在着如下缺点:
a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
c.在DECLARE后,不能再对表变量进行更改;
d.不能对表变量执行INSERT EXEC,SELECT INTO语句;
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。

3.那什么时候可以使用表变量
  要使用表变量应该根据如下规则来判断:
a.表的行数;
b.使用表变量能够减少的重新编译次数;
c.查询的类型和对索引或者统计信息的依赖程度;
d.需要生用UDF,UDDT,XML的时候。
  其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。
  因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。


  下面是一个例子,插入临时表和表变量的数据有20多万行,可以看到,使用临时表的时间是使用表变量所花时间的1/5。

表 'SalesOrderHeader'。扫描计数 3,逻辑读取 130 次,物理读取 9 次,预读 43 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数 3,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 2281 毫秒,占用时间 = 19726 毫秒。
select with temporary table: 20140 ms

********************************************************************************

表 'SalesOrderHeader'。扫描计数 0,逻辑读取 764850 次,物理读取 17 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#4E88ABD4'。扫描计数 1,逻辑读取 12331 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 4375 毫秒,占用时间 = 107160 毫秒。
select with table variable: 107160 ms

4.使用表变量的误区

  对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。

-SQL

CREATE TABLE #TempTable (TT_Col1 INT)

DECLARE @TableVariable TABLE (TV_Col1 INT)

SELECT TOP 2 *

FROM tempdb.sys.objects

ORDER BY create_date DESC




name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003


#03317E3D就是刚创建的表变量;

5.其他

表变量不受rollback影响,某些情况下会破坏数据的完整性。

-SQL

CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
INSERT #TempTable VALUES (1)
INSERT @TableVariable VALUES (1)
BEGIN TRANSACTION
     INSERT #TempTable VALUES (2)
      INSERT @TableVariable VALUES (2)
ROLLBACK
SELECT * FROM #TempTable

/*
TT_Col1
-------
1
*/

SELECT * FROM @TableVariable
--返回了两条记录
/*
TV_Col1
-------
1
2
*/

 

参考:
http://database.ctocio.com.cn/tips/442/8206442.shtml
http://support.microsoft.com/kb/305977/zh-cn
http://blog.csdn.net/buffalo/article/details/96728
http://www.cnblogs.com/gsun/archive/2009/04/08/1431722.html