SQL临时表

最新推荐文章于 2024-08-13 09:09:11 发布

peng21o

最新推荐文章于 2024-08-13 09:09:11 发布

阅读量788

点赞数

文章标签： sql 数据库 object 存储 sql server sqlserver

临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。

临时表有两种类型：

本地临时表
以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表，链接断开时临时表即被删除（本地临时表为创建它的该链接的会话所独享）或者这样说局部临时表是有当前用户创建的，并且只有当前用户的会话才可以访问。

如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建（其实可看作是不同的链接，不同的会话），则数据库引擎必须能够区分由不同用户创建的表。为此，数据库引擎在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 的 sysobjects 表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的 table_name 不能超过 116 个字符。

对于本地临时表来说，需要注意在不同情形下应用本地临时表其删除的实际。如假设数据库在执行一个存储过程的时候建立了本地临时表。那么此时这个本地临时表并不是在会话终止的时候自动删除，而是在这个存储过程执行完毕后就会删除。这是什意思呢?也就是说，用户发起的某个会话，为了执行一个特殊的作业(如用户的这个会话调用了某个存储过程)。此时其实就是会话再创建一个子会话的过程。在这种情况下需要注意的是，子会话创建的本地临时表只在子会话内部有效。当这个子会话终止的时候(存储过程执行完毕)，此时这个临时表就会自动删除。即对于调用这个子会话的会话来说，这个其子会话的创建的临时表对于其也是无效的，因为临时表已经在子会话关闭的时候自动删除。做一个形象的比喻。即现在做父亲的去叫儿子造一座房子。当儿子死亡的时候，这座房子也会消失。对于这种情况，数据库管理员需要注意。父会话只能够引用子会话从临时表中传递出来的数据。也就是说，父会话要访问子会话创建的临时表的数据，只有一种手段。即先让子会话对临时表中的数据进行查询或者操作，然后把结构回传给父会话。父会话是不能够直接访问子会话所创建的临时表。当然这个限制是专门针对本地临时表而言的。对于全局临时表来说，本身就是所有用户都可以访问，为此就没有这个限制。

全局临时表
以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表或者这样说只要这个全局临时表存在，那么用户创建会话后对所有的用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们（换句话说旧的任务还何以引用）。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表即被除去。

--失败的----
declare @sql varchar(100)
set @sql='select id as a,name as b into #b from shopName'
print @sql
exec(@sql)
select * from #b
drop table #b

消息 208，级别 16，状态 0，第 5 行
对象名 '#b' 无效。

---成功的-----
declare @sql varchar(100)
set @sql='select id as a,name as b into ##b from shopName'
print @sql
exec(@sql)
select * from ##b
drop table ##b //一定要DROP不然又要报错

当然勿论是全局的还是本地表，只要是能访问的都能用DROP TABLE 来强制地删除临时表。

当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其他所有约束定义。如果临时表中指定了 FOREIGN KEY 约束，则该语句将返回一条表明已跳过此约束的警告消息。此表仍将创建，但不使用 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

以一个实际的例子来谈谈普通表、本地临时表、全局临时表三个表的差异。如现在有一个保存员工信息的表user。这个表是一个普通表，只要其建立就不会自动删除，任何好在数据库中有使用这个表(具有访问权限)的用户都可以访问这个表，除非这个表被所有者删除或者更改了权限。在用户A(具有访问权限)访问这个表的过程中，数据库可能会根据需要生成一张本地临时表#user。此时只有这个会话才可以访问这个本地临时表。当这个用户的会话中断之后，这个本地临时表也会被自动删除。不过根据需要，数据库也可能会建立全局临时表##user(在名字上与本地临时表不同)。此时数据库中的任何用户只要连接到了数据库就可以访问这个全局临时表(访问权限上的不同)。当这个创建临时表会话的用户中断数据库连接时，这个临时表是否会删除是一个未知数，这要看当时的实际情况(在可用性上不同)。如果此时还有其他用户连接在这个表上的话，那么这个全局临时表就不会被删除。只有在中断连接时，没有其他用户在访问这个表时，即某个用户(不一定是创建这张全局临时表的用户)断开连接并且所有其他的会话不再使用这个表时才会被删除。

　　可见无论是全局临时表还是本地临时表，其跟普通表相比，最重要的一个差异就是其会根据需要自动创建。当不再需要时其又会自动删除。这也正是临时表的魅力所在，其可以在数据处理的过程中，减少很多中间表格。

临时表对日志与锁的影响

　　日志文件是数据库中很重要的一个工具。无论是SQL Server数据库还是Oracle数据库，都有日志这个工具。如凭借重做日志工具，数据库管理员可以在数据库故障的时候借此来恢复数据，将数据恢复到故障的那个点上。但是在使用临时表的时候，需要注意一点，就是临时表不会有日志文件。即对临时表进行的DML等操作不会形成日志文件。这个特性即有好处，也有坏处。好处是对于临时表的更改不会保存到日志文件中。也就是说，如果数据库发生了故障，则保存在临时表中的数据是不能够恢复的。为此数据库管理员不得不重新执行某些作业以重新生成临时表中的数据。好处就是对于临时表的DML操作速度会非常的块。除了其他的原因导致其性能的提升外，在更改其内容时不会生成日志信息也是一个重要的原因。为此对临时表的操作不生成日志信息，这是一个双刃剑。数据库管理员在日常工作中，要尽量发挥其优势，减少其负面作用的影响。

　　另外，若采用临时表这种处理机制的话，还需要注意其对锁的影响。在介绍本地临时表与全局临时表差异的时候，笔者就介绍过，本地临时表只对当前的会话有效。即使当前会话又创建了另外一个子会话，也只对子会话有效。当某个会话终止的时候，这临时表就会自动被删除。而对于普通表或者全局临时表来说，可能同时多个会话都可以访问这个表。这两者有什么区别呢?若允许多个会话可以同时访问某个表的话，那么这个表就可能会遇到锁的情况。即某个用户会话在对表中地记录进行DML等操作时，为了保证数据的一致性，会对相关的记录进行加锁等措施。而采用本地临时表的话，由于只有一个会话可以访问临时表中的数据，所以即使这个会话更改临时表中的数据，也不会有锁冲突的问题。故其在更改本地临时表中的数据时，就不用为其加锁。所以，对于本地临时表的操作速度就要比其他表来的快。故在何时的情况下使用临时表无疑可以提高数据库的整体性能。如可以将一些操作在临时表中完成，然后再将最后的结果更新到基本表中。

利用SQL的全局临时表防止用户重复登录

在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。
　　
　　可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写1，退出时写0，且登录时判断其标志位是否为1，如是则不让该用户工号登录。但是这样那势必会带来新的问题：如发生象断电之类不可预知的现象，系统是非正常退出，无法将标志位置为0，那么下次以该用户工号登录则不可登入.

create procedure gp_findtemptable
/* 寻找以操作员工号命名的全局临时表　　
　　* 如无则将out参数置为0并创建该表,如有则将out参数置为1　　
　　* 在connection断开连接后,全局临时表会被SQL Server自动回收　　
　　* 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,但是已经失去活性　　
　　* 用object_id函数去判断时会认为其不存在.
　　*/
　　@v_userid varchar(6), -- 操作员工号
　　
　　@i_out int out -- 输出参数 0:没有登录 1:已经登录
　　
　　as
　　
　　declare @v_sql varchar(100)
　　
　　if object_id('tempdb.dbo.##'+@v_userid) is null
　　
　　begin
　　
　　set @v_sql = 'create table ##'+@v_userid+'(userid varchar(6))'
　　
　　exec (@v_sql)
　　
　　set @i_out = 0
　　
　　end
　　
　　else
　　
　　set @i_out = 1

在这个过程中，我们看到如果以用户工号命名的全局临时表不存在时过程会去创建一张并把out参数置为0，如果已经存在则将out参数置为1。
　　
　　这样，我们在我们的应用程序中调用该过程时，如果取得的out参数为1时，我们可以毫不客气地跳出一个message告诉用户说”对不起，此工号正被使用！”

上面还涉及到一个OBJECT_ID ()函数：

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . object_name' [ ,'object_type' ] )

一般语法：int object_id('objectname');

此方法返回数据库对象标识号。

其中，参数objectname 表示要使用的对象，其数据类型为nchar或char（如果为char，系统将其转换为nchar）

object_type:为可选参数，其数据类型为nchar或char（如果为char，系统将其转换为nchar），指明架构范围的对象类型（object_name为字符串通过它，可以说明这个字符串究竟是说明对象，其列表见文章结尾）

ps:使用 OBJECT_ID 不能查询非架构范围内的对象（如 DDL 触发器）。对于在 sys.objects 目录视图中找不到的对象，需要通过查询适当的目录视图来获取该对象的标识号。例如，若要返回 DDL 触发器的对象标识号，请使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'。

返回类型为int，表示该对象在系统中的编号，如果找不到或发生错误一律返回NULL。

例子：

A.返回数据库AdventureWorks中Production.WorkOrder表的标识号

USE master;
GO
SELECT OBJECT_ID(N ' AdventureWorks.Production.WorkOrder ') AS ' Object ID ';
GO

B.存在性检查

下列会确认资料表有物件的标识码，藉此检查指定的资料表是否存在。如果存在就删除。

USE AdventureWorks;
GO
IF OBJECT_ID (N ' dbo.AWBuildVersion ', N ' U ') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

此方法一般用来判断数据库中本来用没有此对象（procedures，views，functions等）.

注意：
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。
如果指定一个临时表名，除非当前数据库为tempdb(废话)，否则必须在临时表名前面加上数据库名，例如：
SELECT OBJECT_ID('tempdb..#mytemptable')

Object_Type列表:

AF = 聚合函数 (CLR)

C = CHECK 约束

D = DEFAULT（约束或独立）

F = FOREIGN KEY 约束

FN = SQL 标量函数

FS = 程序集 (CLR) 标量函数

FT = 程序集 (CLR) 表值函数

IF = SQL 内联表值函数

IT = 内部表

P = SQL 存储过程

PC = 程序集 (CLR) 存储过程