首页 > 代码库 > 【T-SQL系列】临时表、表变量

【T-SQL系列】临时表、表变量

原文:【T-SQL系列】临时表、表变量

临时表
临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志,虽它们在Tempdb中体现,是分配在内存中的,它们也支持物理的磁盘,但用户在指定的磁盘里看不到文件。
临时表分为本地和全局两种,本地临时表的名称都是以“#”为前缀,只有在本地当前的用户连接中才是可见的,当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀,创建后对任何用户都是可见的,当所有引用该表的用户断开连接时被删除。
临时表可以创建索引,也可以定义统计数据,所以可以用数据定义语言(DDL)的声明来阻止临时表添加的限制,约束,并参照完整性,如主键和外键约束。
临时表在创建之后可以修改许多已定义的选项,包括:
1)添加、修改、删除列。例如,列的名称、长度、数据类型、精度、小数位数以及为空性均可进行修改,只是有一些限制而已。
2)可添加或删除主键和外键约束。
3)可添加或删除 UNIQUE 和 CHECK 约束及 DEFAULT 定义(对象)。
4)可使用 IDENTITY 或 ROWGUIDCOL 属性添加或删除标识符列。虽然 ROWGUIDCOL 属性也可添加至现有列或从现有列删除,但是任何时候在表中只能有一列可具有该属性。
5)表及表中所选定的列已注册为全文索引。
比较临时表及表变量都可以通过SQL的选择、插入、更新及删除语句,它们的的不同主要体现在以下这些:
  1)表变量是存储在内存中的,当用户在访问表变量的时候,SQL Server是不产生日志的,而在临时表中是产生日志的;
  2)在表变量中,是不允许有非聚集索引的;
  3)表变量是不允许有DEFAULT默认值,也不允许有约束;
  4)临时表上的统计信息是健全而可靠的,但是表变量上的统计信息是不可靠的;
  5)临时表中是有锁的机制,而表变量中就没有锁的机制。

表变量
基本原则:能用表变量就用表变量.实在不行才使用临时表
表变量主要是开销系统的内存,而临时表则使用tempdb.对于小数据量的中间数据存储,可以使用表变量,而当需要临时保存的数据很大时,建议使用临时表.
  表变量创建的语法类似于临时表,区别就在于创建的时候,必须要为之命名。表变量是变量的一种,表变量也分为本地及全局的两种,本地表变量的名称都是以“@”为前缀,只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀,一般都是系统的全局变量,像我们常用到的,如 @@Error代表错误的号,@@RowCount代表影响的行数。
1、为什么要使用表变量
表变量是从2000开始引入的,微软认为与本地临时表相比,表变量具有如下优点:
a.与其他变量的定义一样,表变量具有良好的定义范围,并会被自动清除;
b.在存储过程中使用表变量会减少存储过程重新编译的发生;
c.表变量需要更少的锁请求和日志资源;
d.可以在表变量上使用UDF,UDDT,XML。
2、表变量的限制
与临时表相比,表变量存在着如下缺点:
a.在表变量上没有统计信息,查询优化器根据固定的预估值来选择执行计划,在数据很多的情况下,会导致查询优化器选择很差的执行计划;
b.不能直接在表变量上创建索引,但可以通过创建约束(主键、唯一)来建立索引;
c.在DECLARE后,不能再对表变量进行更改;
d.不能对表变量执行INSERT EXEC , SELECT INTO语句(只针对05前的版本);
e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句,但如果表变量是在动态SQL语句内定义的,则可以。
3、那什么时候可以使用表变量
要使用表变量应该根据如下规则来判断:
a.表的行数;
b.使用表变量能够减少的重新编译次数;
c.查询的类型和对索引或者统计信息的依赖程度;
d.需要生用UDF,UDDT,XML的时候。
其实也就说,得从实际出发,根据具体的查询,作出具体的选择。但是,其中很关键的一点,如果表的行数非常多,使用表变量其实是更费资源的。有人提出了这样的建议:对于行数较少的情况下(小于1000行)可以使用表变量;如果行数很多(有几万行),则使用临时表。
因此,在实际的开发中,应通过分别使用临时表或表变量进行对比后,才作出决定。
4、使用表变量的误区
对于表变量,很多人认为,表变量和其他变量一样,只存在内存中,其实这是不正确的,表变量也存在tempdb中。可以通过下面例子进行对比。

CREATE TABLE #TempTable ( TT_Col1 INT )DECLARE @TableVariable TABLE ( TV_Col1 INT )SELECT TOP 2        *FROM    tempdb.sys.objectsORDER BY create_date DESCDROP TABLE #TempTable

 

5、其他
由于表变量作用域有限,并且不是持久数据库的一部分,因而不受事务回滚的影响。
表变量不受rollback影响,某些情况下会破坏数据的完整性。

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

 

【T-SQL系列】临时表、表变量