sql 临时表

在使用T-SQL编写触发器或者函数的时候，经常需要用到一个临时表。SQL Server的临时表是存储在tempdb中的。

临时表的创建、使用、删除等与普通表相似，只是在名字上、可见性和使用性上会有差别。关于使用临时表需要注意的事项在下面解释，先看看临时表的创建、使用、和删除的代码吧！

创建临时表

create table TempTableName   ---TempTableName 必须以# 或者##开头 
( 
ID int IDENTITY (1,1) not null, 
a1 varchar(50), 
a2 varchar(50), 
a3 varchar(50), 
primary key (ID) --定义ID为临时表#Tmp的主键 
) 

临时表的使用

select * from TempTableName

删除临时表

drop table TempTableName

 

一、临时表的两种表现形式。

在SQL Server数据库中，临时表主要有两种形式，分别为全局临时表与局部临时表。这两种表有很大的不同，主要体现在名字上、可见性上以及可用性上。具体来说，本地临时表的名字是以#符号开头的;而全局临时表则是以##两个#字符号开头。从可见性上来说，局部临时表是有当前用户创建的，并且只有当前用户的会话才可以访问。而对于全局表来说，只要这个临时表存在，那么用户创建会话后对所有的用户都是可见的。两者在删除的时机尚也有不同。如本地临时表在当前用户中断会话后这个临时表就会被删除。而全局临时表只有当引用这个表的用户从数据库中断开连接时才会被删除。由于这两种临时表存在这么多的差异，数据库管理员就需要根据实际应用来确定采用合适的临时表类型。

 

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

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

二、使用临时表的好处。

在T-SQL语言中使用临时表的好处是很显而易见的。笔者下面就举一些常见的好处。

如利用临时表来组织数据，比普通表会更加的简洁、紧凑。这主要是在临时表中可以实现很多的特性。如可以进行预处理计算。如当发现基本标中的索引不怎么合适，也可以在数据库临时表中重新创建索引以优化原有的索引。特别是当需要多次访问某个表或者视图的时候，利用临时表来组织数据是一个提高效率的好方法。即使只是一个简单的查询，其效率的提升也是很明显的。为此，使用临时表最明显的一个好处就似乎可以提高数据库的性能，特别是查询的性能。

另外使用临时表还可以减少中间表的产生。在进行某些操作时，本来往往需要一些中间表的帮助才可以完成。而现在数据库管理员可以让数据库在需要时自动生成中间表，并在用完后进行自动删除。如此的话，中间表的建立与删除就不需要数据库管理员人为的管理了。所以，使用临时表可以减少数据库系统中的垃圾表，也可以降低用户的工作量。为此笔者认为，临时表是SQL Server数据库中一个很使用的工具。作为数据库管理员，要在平时的工作中，合理使用这个临时表，发挥其最大的效用。虽然针对特定的任务该采用什么类型的临时表，有很多容易混淆的地方。但是数据库管理员不能够因噎废食，而应该积极的去尝试。

三、要对本地临时表特别引起重视。

在平时的应用与管理中，本地临时表的应用几率要比全局临时表多的多。而且本地临时表由于只有用户自己的会话可以进行访问，而全局临时表则是所有用户都可以访问。为此在安全性上本地临时表也要比全局临时表高的多。为此笔者认为数据库管理员主要要掌握本地临时表的应用技巧。然后再对比的去了解全局临时表的信息，这可能是学习SQLServer数据库临时表的一个捷径。

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

四、临时表对日志与锁的影响。

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

 

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