SQL Server临时表

转帖一： 临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。 临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。 例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

转帖二：

可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。

本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。

SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1)  

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

除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：

• 当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。
• 所有其它本地临时表在当前会话结束时自动除去。
• 全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。

在存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所创建的表，例如：

CREATE PROCEDURE Test2 AS CREATE TABLE #t(x INT PRIMARY KEY) INSERT INTO #t VALUES (2) SELECT Test2Col = x FROM #t GO CREATE PROCEDURE Test1 AS CREATE TABLE #t(x INT PRIMARY KEY) INSERT INTO #t VALUES (1) SELECT Test1Col = x FROM #t EXEC Test2 GO CREATE TABLE #t(x INT PRIMARY KEY) INSERT INTO #t VALUES (99) GO EXEC Test1 GO  

下面是结果集：

(1 row(s) affected) Test1Col ----------- 1 (1 row(s) affected) Test2Col ----------- 2  

当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供更有效的查询处理。 

 

转帖3

SQL SERVER临时表实用大全

2008-07-22 10:47

1.SQL SERVER临时表实用大全 老蓝笔记整理： 引子：             临时数据表格，我们在存储的时候经常遇见。 　　客户端可以实用Delphi的ClientDataSet的内存表，但是ClientDataSet类似TABLE，不是支持SQL语言的。 　　当然也可以实用临时表。有时我们避免临时表的生命周期的麻烦，更多的使用实际表临时用用。起个什么tempXXX类似的告诉我们他们是临时表。创建释放也没有问题。 　　那么临时表的周期是什么样子的呢？有什么特殊的用法没有？ 1.全局临时表和临时表的区别：视野不同。 　　　全局临时表当然就是使用##打头的表格，普通的临时表格为#打头。它们的周期应该随一个连接也就是Connection的诞生而生，随着Connection的断开而死亡。它们的区别不同的地方也就是视野不同。 　　　全局表，全部授权的Connection都可以看见。但是普通临时表（局域临时表）仅仅创建的Connection可以看见。特别说明的是sql server的isql.exe每个连接就是一个Connection。 　　　例如：一个应用程序app.exe有仅adoconnection连接SQL SERVER数据，那么这个连接中的adoquery或者存储过程建立的全局表##temp1，另外的一个app.exe或者其它的appx.exe都可以使用该表格。 　　　不同的是普通临时表，由app.exe中的连接connection比如adoconnection的Adoquery建立的临时表#temp，在所有该连接的该程序的app.exe都可以看见并且访问，但是appx.exe或者其它的就不能看见。            陷阱：如果一个应用程序app.exe有存储过程和TADOQUERY连接同一个ADOCONNECTION.切记使用存储过程建立的临时表（非全局），就不能被这个连接下的TADOQUERY访问。原因是：存储过程本身是服务器端执行，应该是服务器的CONNECTION。 2.全局表或者临时表的存储和建立。 　　　估计大家都知道，临时表或者全局表都是在sql server的tempdb数据库存储，表我们访问的时候使用#，但是实际在进程里面是以在tempdb里面的用户表以#xxx_________00000000xxx方式存储（是否是进程标识，不大知道，也不想知道）。可以通过isql.exe的tempdb里面看见它们。 3。头痛初始化表格 　　初始化表格，为什么头痛，毕竟使用Drop什么的。或者判断存在比较麻烦。其实也很简单。另外建立临时表，都是无聊的SQL 语句，怎么处理。 方法一：当然是使用存储过程了。可以直接写SQL语句或者写exec (@v_sql)。 方法二：把临时表保存到数据里面MEMO里面，读取，直接执行。 　　判断方法一：可以采用常用的exists函数。 　　　例如：if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")     　　判断方法二： 　　if object_id('tempdb.dbo.##'+@v_userid) is null 应用方法一：（感谢阿满，迷糊）     一个统计的临时表格，使用TADOPROC执行存储过程，如下： CREATE PROCEDURE InitCreateCoawardTable AS BEGIN     IF not (object_id('tempdb.dbo.#Tmp_Detail') is null)      drop table tempdb.dbo.#Tmp_Detail      CREATE TABLE tempdb.dbo.#Tmp_Detail ( [id] [int] IDENTITY (1, 1) NOT NULL , [单据类别] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [单据编号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , （。。这里多复杂都可以。。。。。省略。。。）     select * from tempdb.dbo.#Tmp_Detail END GO 在客户端使用TADOPROC执行这个存储过程，     with sproc do     begin       try        Close;        ProcedureName:=spName;        Parameters.Refresh;        Prepared;        ExecProc;        Open;//这句不能省略要不，就出现忽略了数据库名称 '，将引用 tempdb 中的对象错误。        except          Close;          Exit;        end;        Result:=True;     end; 这样，你的TADOPROC就可以任意使用APPEND,INSERT语句了，更好的是不需要清理，而且方法灵活。更好的有点非常有效的处理并发。当然有些人还在使用建立实际表格代替它，使用会话处理该问题，你还得回收。相当麻烦。而且灵活度差多了。   以下是应用范例转载： 利用SQL Server的全局临时表防止用户重复登录　　　 在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。         可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写1，退出时写0，且登录时判断其标志位是否为1，如是则不让该用户工号登录。但是这样那势必会带来新的问题：如发生象断电之类不可预知的现象，系统是非正常退出，无法将标志位置为0，那么下次以该用户工号登录则不可登入，这该怎么办呢？         或许我们可以换一下思路：有什么东西是在connection断开后可以被系统自动回收的呢？对了，SQL Server的临时表具备这个特性！但是我们这里的这种情况不能用局部临时表，因为局部临时表对于每一个connection来说都是一个独立的对象，因此只能用全局临时表来达到我们的目的。         好了，情况已经明朗话了，我们可以写一个象下面这样简单的存储过程:   create procedure gp_findtemptable -- 2001/10/26 21:36 zhuzhichao in nanjing /* 寻找以操作员工号命名的全局临时表 * 如无则将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告诉用户说”对不起，此工号正被使用！” 判断方法范例： select        @sTmpWareA="tempdb..[##MARWareA"+        @ComputerName+"]"       if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")                 begin                     set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"                         exec(        "drop        table        "        +@sTmpWareA        )                 end   else                     set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"