SQL基础:SQL Server中使用表变量和临时表
一、表变量
表变量在SQL Server 2000中首次被引入。
表变量的具体定义包括列定义,列名,数据类型和约束。而在表变量中可以使用的约束包括主键约束,唯一约束,NULL约束和CHECK约束(外键约束不能在表变量中使用)。
定义表变量的语句是和正常使用Create Table定义表语句的子集。只是表变量通过DECLARE @local_variable语句进行定义。
1、定义和使用表变量
定义一个表变量,插入一条数据,然后查询:
(
Id int,
Name varchar(20),
Age int
)
INSERT INTO @tb1 VALUES(1,’刘备’,22)
SELECT * FROM @tb1
输出结果如下:
2、表变量不能做如下事情:
来试试一些不符合要求的情况,例如添加表变量后,添加约束,并对约束命名:
CHECK
(Account_Age > 18); — 插入年龄必须大于18
SQL Server提示错误如下:
SQL Server不支持定义表变量时对Constraint命名,也不支持定义表变量后,对其建Constraint。
更多的不允许,请查看下面的要求。
- 虽然表变量是一个变量,但是其不能赋值给另一个变量。
- check约束,默认值和计算列不能引用自定义函数。
- 不能为约束命名。
- 不能Truncate表变量。
- 不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)
3、表变量的特征:
- 表变量拥有特定作用域(在当前批处理语句中,但不在任何当前批处理语句调用的存储过程和函数中),表变量在批处理结束后自动被清除。
- 表变量较临时表产生更少的存储过程重编译。
- 针对表变量的事务仅仅在更新数据时生效,所以锁和日志产生的数量会更少。
- 由于表变量的作用域如此之小,而且不属于数据库的持久部分,所以事务回滚不会影响表变量。
表变量可以在其作用域内像正常的表一样使用。更确切的说,表变量可以被当成正常的表或者表表达式一样在SELECT,DELETE,UPDATE,INSERT语句中使用,但是表变量不能在类似"SELECT select_list INTO table_variable"这样的语句中使用。而在SQL Server2000中,表变量也不能用于INSERT INTO table_variable EXEC stored_procedure这样的语句中。
二、临时表
在深入临时表之前,我们要了解一下会话(Session),一个会话仅仅是一个客户端到数据引擎的连接。在SQL Server Management Studio中,每一个查询窗口都会和数据库引擎建立连接。
一个应用程序可以和数据库建立一个或多个连接,除此之外,应用程序还可能建立连接后一直不释放知道应用程序结束,也可能使用完释放连接需要时建立连接。
临时表和Create Table语句创建的表有着相同的物理工程,但临时表与正常的表不同之处有:
- 临时表的名称不能超过116个字符,这是由于数据库引擎为了辨别不同会话建立不同的临时表,所以会自动在临时表的名字后附加一串。
- 局部临时表(以"#"开头命名的)作用域仅仅在当前的连接内,从在存储过程中建立局部临时表的角度来看,局部临时表会在下列情况下被Drop:
a、显示调用Drop Table语句
b、当局部临时表在存储过程内被创建时,存储过程结束也就意味着局部临时表被Drop。
c、当前会话结束,在会话内创建的所有局部临时表都会被Drop。 - 全局临时表(以"##"开头命名的)在所有的会话内可见,所以在创建全局临时表之前首先检查其是否存在,否则如果已经存在,你将会得到重复创建对象的错误。
a、全局临时表会在创建其的会话结束后被Drop,Drop后其他会话将不能对全局临时表进行引用。
b、引用是在语句级别进行 - 不能对临时表进行分区。
- 不能对临时表加外键约束。
- 临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象,而临时表属于TempDb)。
由于TempDb在每次SQL Server重启后会被自动创建,所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。 - XML列不能定义成XML集合的形式,除非这个集合已经在TempDb中定义。
临时表既可以通过Create Table语句创建,也可以通过"SELECT <select_list> INTO #table"语句创建。你还可以针对临时表用"INSERT INTO #table EXEC stored_procedure"这样的语句。
临时表可以拥有命名的约束和索引。但是,当两个用户在同一时间调用同一存储过程时,将会产生”There is already an object named ‘<objectname>’ in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名,而使用系统分配的在TempDb中唯一的。
1、全局临时表引用是在语句级别进行
如:
1.新建查询窗口,运行语句:
INSERT INTO ##temp VALUES(3)
2.再次新建一个查询窗口,每5秒引用一次全局临时表
BEGIN
SELECT * FROM ##temp
WAITFOR delay ’00:00:05′
END
3.回到第一个窗口,关闭窗口。
4.下一次第二个窗口引用时,将产生错误。
三、比较
微软推荐使用表变量,如果表中的行数非常小,则使用表变量。
临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况,你都需要考虑其各自优缺点并做一些性能测试。
下面的表格会让你比较其优略有了更详细的参考。
特性 | 表变量 | 临时表 |
---|---|---|
作用域 | 当前批处理 | 当前会话,嵌套存储过程, 全局:所有会话 |
使用场景 | 自定义函数,存储过程,批处理 | 自定义函数,存储过程,批处理 |
创建方式 | 只能通过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之前定义临时表 |
到此这篇关于SQL Server中使用表变量和临时表的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持。