SQL Server临时表如何定义

发布时间：2024-01-01 12:14:33 所属栏目：MsSql教程来源：DaWei

导读： 在深入临时表之前，我们要了解一下会话(Session)，一个会话仅仅是一个客户端到数据引擎的连接。在SQL Server Management Studio中，每一个查询窗口都会和数据库引擎建立连接。

一个应用程

在深入临时表之前，我们要了解一下会话(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、全局临时表引用是在语句级别进行

a.新建查询窗口，运行语句：
CREATE TABLE ##temp(RowID int)
INSERT INTO ##temp VALUES(3)

b.再次新建一个查询窗口，每5秒引用一次全局临时表
While 1=1
　　BEGIN
　　SELECT * FROM ##temp
　　WAITFOR delay '00:00:05'
　　END

c.回到第一个窗口，关闭窗口。

d.下一次第二个窗口引用时，将产生错误。

比较

微软推荐使用表变量，如果表中的行数非常小，则使用表变量。

临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况，你都需要考虑其各自优缺点并做一些性能测试。

特性   表变量   临时表
作用域   当前批处理   当前会话，嵌套存储过程，
全局：所有会话
使用场景   自定义函数，存储过程，批处理   自定义函数，存储过程，批处理
创建方式   只能通过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之前定义临时表

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!