表变量和临时表详解

首先让我们来看看什么是表变量和临时表。

sql server 表变量

1.初识表变量
　　表变量在sql server 2000中首次被引用。表变量的定义和创建一个表大致相同，只不过是使用DECLARE @variable而不是CREATE Table，表变量定义包括列定义，列名，数据类型和约束（可用的约束有：主键约束、唯一约束、null约束、和CHECK约束。注：外键约束不可以哦）。表变量和其他变量不同，它不止存在于内存中，还存在于tempdb中。

2.表变量优点
　　1).表变量拥有特定的作用域（在当前批处理中），表变量在批处理结束后自动被清除。
　　2).在存储过程中使用表变量较临时表会减少存储过程重新编译的发生。
　　3).表变量需要更少的锁请求和日志资源。
　　4).可以在表变量上使用UDF,UDDT,XML

3.表变量的缺点
　　1).在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据量很大的情况下，会导致查询优化器选择很差的执行计划。
　　2).不能在表变量上创建索引，但是可以通过创建约束（主键、唯一）来建立索引；
　　3).在 DECLARE 之后，不能再对表变量进行更改；
　　4).不能对表变量使用 SELECT INTO，INSERT EXEC 语句；
　　5).不能通过EXEC或sp_executeSQL 来执行牵涉表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。

4.何时使用表变量（可以根据以下规则来判断何时使用表变量）
　　1).表的行数
　　2).使用表变量能够减少的重新编译次数
　　3).查询的类型和对索引或者统计信息的依赖程度
　　4).是否需要使用UDF,UDDF,XML

5.表变量不能做的事
　　1).虽然表变量是一个变量，但是其不能赋值给另一个变量。
　　2).check约束、默认值、计算列不能引用自定义函数。
　　3).不能为约束命名
　　4).不能truncate 表变量
　　5).不能向标识列中插入显示值（也就是说表变量不支持SET IDENTITY_INSERT ON）

 

sql server 临时表

临时表和Create Table语句创建的表的不同之处在于：
　　1).临时表的名称不能超过116个字符，这是由于数据库引擎为了辨别不同会话建立的临时表，会自动在临时表名字后附加一串。
　　2).局部临时表作用域仅仅在当前的连接内，从存储过程中建立局部临时表的角度来看，局部临时表会在以下情况下别Drop：
　　　　a.显示调用DROP TABLE语句
　　　　b.当局部临时表在存储过程内被创建时，存储过程结束就意味着局部临时表被Drop
　　　　c.当前会话结束，在会话内创建的所有局部临时表都会被DROP。
　　3).全局临时表在所有的会话内可见，但是在其创建的会话结束后即被drop，drop后其它会话也将不能对其进行引用。
　　4).不能对临时表进行分区。
　　5).不能对临时表加外键约束。
　　6).临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象，而临时表属于TempDb)。由于TempDb在每次SQL 　　Server重启后会被自动创建，所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。
　　7).XML列不能定义成XML集合的形式，除非这个集合已经在TempDb中定义。

 

　　至于表变量和临时表的区别，在介绍表变量时所提到的优缺点中基本概括了，另外还有一个区别是体现在排序规则上：表变量使用当前数据库的排序规则，临时表使用TempDb的排序规则。如果它们不兼容，你还需要在查询或者表定义中进行指定。

　　下面是看到的别人整理的一张表格，看着清晰一点。

总结

 

特性	表变量	临时表
作用域	当前批处理	当前会话，嵌套存储过程，全局：所有会话
使用场景	自定义函数，存储过程，批处理	自定义函数，存储过程，批处理
创建方式	DECLARE statement only.只能通过DECLEARE语句创建	CREATE TABLE 语句 SELECT INTO 语句.
表名长度	最多128字节	最多116字节
列类型	可以使用自定义数据类型 可以使用XML集合	自定义数据类型和XML集合必须在TempDb内定义
Collation	字符串排序规则继承自当前数据库	字符串排序规则继承自TempDb数据库
索引	索引必须在表定义时建立	索引可以在表创建后建立
约束	PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用，但必须在表建立时声明	PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用，可以在任何时后添加，但不能有外键约束
表建立后使用DDL (索引，列)	不允许	允许.
数据插入方式	INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC).	INSERT 语句, 包括 INSERT/EXEC. SELECT INTO 语句.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	不支持SET IDENTITY_INSERT语句	支持SET IDENTITY_INSERT语句
Truncate table	不允许	允许
析构方式	批处理结束后自动析构	显式调用 DROP TABLE 语句.  当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)
事务	只会在更新表的时候有事务，持续时间比临时表短	正常的事务长度，比表变量长
存储过程重编译	否	会导致重编译
回滚	不会被回滚影响	会被回滚影响
统计数据	不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准	创建统计数据，通过实际的行数生成执行计划。
作为参数传入存储过程	仅仅在SQL Server2008, 并且必须预定义 user-defined table type.	不允许
显式命名对象 (索引, 约束).	不允许	允许，但是要注意多用户的问题
动态SQL	必须在动态SQL中定义表变量	可以在调用动态SQL之前定义临时表