临时表的使用方案大全.docx

SQL Server临时表的使用方案大全 2010-07-22 15:56 佚名 博客园 我要评论(0) 摘要：我们今天主要描述的是SQL Server临时表的实用大全，我在一个网站找到一个关于SQL Server临时表的实用大的资料，所以今天拿出来供大家分享。 标签：SQL Server临时表 限时报名参加“甲骨文全球大会2010北京”及“JavaOne和甲骨文开发者大会2010”我们今天是要和大家一起讨论的是SQL Server临时表的实用大全，如果你对SQL Server临时表的实用大全不是很了解的话，下面的文章就是对其相关内容的具体介绍，希望会给你带来一些帮助在此方面。引子：临时数据表格，我们在存储的时候经常遇见。客户端可以实用Delphi的ClientDataSet的内存表，但是ClientDataSet类似TABLE，不是支持SQL语言的。当然也可以实用临时表。有时我们避免SQL Server临时表的生命周期的麻烦，更多的使用实际表临时用用。起个什么tempXXX类似的告诉我们他们是临时表。创建释放也没有问题。那么临时表的周期是什么样子的呢？有什么特殊的用法没有？1.全局临时表和临时表的区别：视野不同。全局临时表当然就是使用#打头的表格，普通的临时表格为#打头。它们的周期应该随一个连接也就是Connection的诞生而生，随着Connection的断开而死亡。它们的区别不同的地方也就是视野不同。全局表，全部授权的Connection都可以看见。但是普通SQL Server临时表（局域临时表）仅仅创建的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临时表的存储和建立。估计大家都知道，临时表或者全局表都是在SQL Server的tempdb数据库存储，表我们访问的时候使用#，但是实际在进程里面是以在tempdb里面的用户表以#xxx_00000000xxx方式存储（是否是进程标识，不大知道，也不想知道）。可以通过isql.exe的tempdb里面看见它们。3。头痛初始化表格初始化表格，为什么头痛，毕竟使用Drop什么的。或者判断存在比较麻烦。其实也很简单。另外建立临时表，都是无聊的SQL 语句，怎么处理。方法一：当然是使用存储过程了。可以直接写SQL语句或者写exec (v_sql)。方法二：把SQL Server临时表保存到数据里面MEMO里面，读取，直接执行。判断方法一：可以采用常用的exists函数。例如：1. ifexists(select*fromtempdb.sysobjectswhereid=object_id(sTmpWareA)andtype=U)判断方法二：1. ifobject_id(tempdb.dbo.#+v_userid)isnull应用方法一：（感谢阿满，迷糊）一个统计的SQL Server临时表格，使用TADOPROC执行存储过程，如下：1. CREATEPROCEDUREInitCreateCoawardTableAS 2. BEGIN 3. IFnot(object_id(tempdb.dbo.#Tmp_Detail)isnull) 4. droptabletempdb.dbo.#Tmp_Detail 5. CREATETABLEtempdb.dbo.#Tmp_Detail( 6. idintIDENTITY(1,1)NOTNULL,单据类别 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,单据编号 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,（。这里多复杂都可以。省略。）1. select*fromtempdb.dbo.#Tmp_Detail 2. END 3. GO在客户端使用TADOPROC执行这个存储过程，1. withsprocdo 2. begin 3. try 4. Close; 5. ProcedureName:=spName; 6. Parameters.Refresh; 7. Prepared; 8. ExecProc;Open;/这句不能省略要不，就出现忽略了数据库名称 ，将引用 tempdb 中的对象错误。1. except 2. Close; 3. Exit; 4. end; 5. Result:=True; 6. end;这样，你的TADOPROC就可以任意使用APPEND,INSERT语句了，更好的是不需要清理，而且方法灵活。更好的有点非常有效的处理并发。当然有些人还在使用建立实际表格代替它，使用会话处理该问题，你还得回收。相当麻烦。而且灵活度差多了。以下是应用范例转载：利用SQL Server的全局临时表防止用户重复登录在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写1，退出时写0，且登录时判断其标志位是否为1，如是则不让该用户工号登录。但是这样那势必会带来新的问题：如发生象断电之类不可预知的现象，系统是非正常退出，无法将标志位置为0，那么下次以该用户工号登录则不可登入，这该怎么办呢？或许我们可以换一下思路：有什么东西是在connection断开后可以被系统自动回收的呢？对了，SQL Server的临时表具备这个特性！但是我们这里的这种情况不能用局部临时表，因为局部临时表对于每一个connection来说都是一个独立的对象，因此只能用全局SQL Server临时表来达到我们的目的。好了，情况已经明朗话了，我们可以写一个象下面这样简单的存储过程:1. createproceduregp_findtemptable-2001/10/2621:36zhuzhichaoinnanjing/* 寻找以操作员工号命名的全局临时表* 如无则将out参数置为0并创建该表,如有则将out参数置为1* 在connection断开连接后,全局临时表会被SQL Server自动回收* 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,但是已经失去活性* 用object_id函数去判断时会认为其不存在. */v_userid varchar(6), - 操作员工号i_out int out - 输出参数 0:没有登录 1:已经登录1. as 2. declarev_sqlvarchar(100) 3. ifobject_id(tempdb.dbo.#+v_userid)isnull 4. begin 5. setv_sql=createtable#+v_userid+(useridvarchar(6) 6. exec(v_sql) 7. seti_out=08. end 9. else 10. seti_out=1在这个过程中，我们看到如果以用户工号命名的全局SQL Server临时表不存在时过程会去创建一张并把out参数置为0，如果已经存在则将out参数置为1。这样，我们在我们的应用程序中调用该过程时，如果取得的out参数为1时，我们可以毫不客气地跳出一个message告诉用户说”对不起，此工号正被使用！”判断方法范例：1. selectsTmpWareA=tempdb.#MARWareA+ComputerName+ 2. ifexists(select*fromtempdb.sysobjectswhereid=object_id(sTmpWareA)andtype=U) 3. begin 4. setsTmpWareA=#MARWareA+ComputerName+ 5. exec(droptable+sTmpWareA) 6. end 7. else 8. setsTmpWareA=#MARWareA+ComputerName+sTmpWareA 就是SQL Server临时表的名称，过程中使用exec来操作SQL Server学习笔记12之-临时表和表变量今天来说说 临时表和表变量当我们需要把数据临时具体化的时候,你会想到临时表,表变量或者表表达式处理成这样的好处有几个：1.处理临时出来的表 比基本数据肯定要简洁。特别是你多次利用它的时候,你可以想象经常从一张大表里找东西有多浩大的工程.2.处理成临时数据集 可以让你的代码,思路清晰无比. 有些代码比较长,如果你分模块逐个处理会很清楚.这里临时数据集起到很重要的作用.A.临时表1. 你建的临时表都是放在TEMPDB中。所以当我们用临时数据集的时候是可以减轻你那个用户数据库的压力的。2. 你的临时表在判断是否存在时,要用IF OBJECT_ID(TEMPDB.#t) is not null -注意这里一定要用TEMPDB.临时表名.3. 你在一个会话建立的临时表只在该会话有效.意思就是你可以在另外一个会话建立同名的一个临时表.你也许会奇怪,sql 内部 又是怎么知道哪个是哪个临时表的呢？其实你每次建立一个临时表,它就会在tempdb中为表名加上下划线和数字后缀.如： 我分别在MASTER和PUBS库中分别建立了1个临时表#p view plaincopy to clipboardprint?use tempdb go select namefrom sys.objects where name like #p%/*name-#p_00000000000C#p_000000000011*/ use tempdb goselect namefrom sys.objects where name like #p%/*name-#p_00000000000C#p_000000000011*/ 4.临时表只有在创建他的那个级别和内部级别可见.什么意思呢？就是说如果你在一个存储过程内部建立的临时表在存储过程外面是看不到的。当你存储过程执行完了,这个临时表就玩完了.而你在会话的最外层建立的临时表 那再找个会话中的任何地方都是可以用它的.只有当会话关掉的时候,它才结束.-| 当然你可以手动删除它.这里就可以引出一个有意思的问题:既然临时表可见度不一致,那么我们就可以在一个会话里建立同名的临时表了,只要创建级别不一样.但是还是会那么点问题.首先举个例子来说明这个问题引用书上例子,比较简洁)view plaincopy to clipboardprint?SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID(c1) IS NOT NULL DROP PROC c1; GO IF OBJECT_ID(c2) IS NOT NULL DROP PROC c2; GO CREATE PROC c1 AS CREATE TABLE #T1(col1 INT NOT NULL); INSERT INTO #T1 VALUES(1); SELECT * FROM #T1; EXEC c2;-注意这里我调用了RPOC2 GO CREATE PROC c2 AS CREATE TABLE #T1(col1 INT NULL);-这里再次创建了#T1 INSERT INTO #T1 VALUES(2); SELECT * FROM #T1; GO - 调用存储过程1 EXEC c1; GO /*col1-1col1-2*/SET NOCOUNT ON;USE tempdb;GOIF OBJECT_ID(c1) IS NOT NULL DROP PROC c1;GOIF OBJECT_ID(c2) IS NOT NULL DROP PROC c2;GOCREATE PROC c1ASCREATE TABLE #T1(col1 INT NOT NULL);INSERT INTO #T1 VALUES(1);SELECT * FROM #T1;EXEC c2;-注意这里我调用了RPOC2GOCREATE PROC c2ASCREATE TABLE #T1(col1 INT NULL);-这里再次创建了#T1INSERT INTO #T1 VALUES(2);SELECT * FROM #T1;GO- 调用存储过程1EXEC c1;GO/*col1-1col1-2*/-这里似乎没出问题.这里他们的表结构式一样的,注意.-现在情况变了,我去修改下第二个存储过程中#T1的表结构,看看情况还是否一样.view plaincopy to clipboardprint?ALTER PROC c2 AS CREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL); INSERT INTO #T1 VALUES(2, 2); SELECT * FROM #T1; GO - 运行,出现错误. EXEC c1; GO /*col1-1 消息 213，级别 16，状态 1，过程 proc2，第 5 行列名或所提供值的数目与表定义不匹配。*/ALTER PROC c2ASCREATE TABLE #T1(col1 INT NULL, col2 INT NOT NULL);INSERT INTO #T1 VALUES(2, 2);SELECT * FROM #T1;GO- 运行,出现错误.EXEC c1;GO/*col1-1消息 213，级别 16，状态 1，过程 proc2，第 5 行列名或所提供值的数目与表定义不匹配。*/-这里的错误明显发生在PROC1调用PROC2的时候.这个错误情况看来,貌似我们的#t1表的列数还是1列 而不是2列.解释：知识前提：当你的批处理被解析时候,你创建的临时表是不可以用的,它要等到运行时候才被解析.如果你引用的临时表已经存在了,它不会智能地去帮你判断下你本身是否有这么个临时表,而是直接去解析那个已经存在的临时表.然后就悲剧般的使用已经存在的表.我们回到我们这个题目,当PROC2被PROC1调用的时候,因为#t1表已经在PROC1中存在了,那么它在解析你的PROC2中的INSERT INTO #T1 VALUES(2, 2)的时候去插入的是PROC1中的#T1。这个时候你就报错了.-我们的解决方法就是：先去运行下PROC2 然后在执行EXEC c1; 就是你要的了.这是因为先执行PROC2，他的执行计划就存在缓存里了.这个问题就告诉我们在不同级别里取相同名字的临时表也是不明智的.5.临时表是属于事务的组成部分,因此是否把它当成外部事务的一部分操作,时你考虑是否选择临时表的一个重要因素.6.临时表的分部统计信息是被优化器维护的,它可以生成优化的计划,这也是区别于表变量的一个特性之一. 这里要注意点：就像存储过程一样,临时表到了一定程度的改变后,它需要重新编译.这里简单介绍下达到重新编译的标准. 重新编译阀值(RT)是根据表的类型和行数所确定的. a-持久表:n500,RT=500+0.2*n; (n是表的基数) b-临时表:n=6的时候,RT=6 ; 6n500,RT=500+0.2*n比如你在临时表了加载了6行,当增加第7行的时候就会导致重新编译7.动态中更改临时表的架构(表结构) view plaincopy to clipboardprint?DECLARE schema AS VARCHAR(1000), insert AS VARCHAR(1000); SET schema = col1 INT, col2 DECIMAL(10, 2); SET insert = INSERT INTO #T42 VALUES(10, 20.30); CREATE TABLE #T42(dummycol INT); - 会话外层创建临时表 - Alter #T 增加你需要的列 - Alter #T 删除不需要的列 - 插入数据 EXEC( ALTER TABLE #T42 ADD + schema + ; ALTER TABLE #T42 DROP COLUMN dummycol; + insert ); GO SELECT * FROM #T42;DECLARE schema AS VARCHAR(1000), insert AS VARCHAR(1000);SET schema = col1 INT, col2 DECIMAL(10, 2);SET insert = INSERT INTO #T42 VALUES(10, 20.30);CREATE TABLE #T42(dummycol INT);- 会话外层创建临时表- Alter #T 增加你需要的列- Alter #T 删除不需要的列- 插入数据EXEC(ALTER TABLE #T42 ADD + schema + ;ALTER TABLE #T42 DROP COLUMN dummycol; + insert );GOSELECT * FROM #T42; -这个时候会出现-列名或所提供值的数目与表定义不匹配-的错误.说明不能使用刚才修改过的表结构.这个原因我在上面说过了,因为外层还有一个#t 它在-解析这个动态的INSERT时候，会找到这个会话已经拥有的#T,而新的表结构是没用到的.-解决方法-你可以人为制造一个更加低级的环境,我的意思就是在这个动态内部加个动态,这样他要去解析的时候再里面找不到就跑到最接近他的那层.这样他就可以解析到新的那个表结构view plaincopy to clipboardprint?DECLARE schema AS VARCHAR(1000), insert AS VARCHAR(1000); SET schema = col1 INT, col2 DECIMAL(10, 2); SET insert = INSERT INTO #T42 VALUES(10, 20.30); CREATE TABLE #T42(dummycol INT); EXEC( ALTER TABLE #T42 ADD + schema + ; ALTER TABLE #T42 DROP COLUMN dummycol; EXEC( + insert + ); GO SELECT * FROM #T42; - /*col1 col2- -10 20.30*/DECLARE schema AS VARCHAR(1000), insert AS VARCHAR(1000);SET schema = col1 INT, col2 DECIMAL(10, 2);SET insert = INSERT INTO #T42 VALUES(10, 20.30);CREATE TABLE #T42(dummycol INT);EXEC(ALTER TABLE #T42 ADD + schema + ;ALTER TABLE #T42 DROP COLUMN dummycol;EXEC( + insert + );GOSELECT * FROM #T42;-/*col1 col2- -10 20.30*/8.全局临时表:它用在会话之间共享数据又没安全担心的情况下。它跟局部临时表不同之处在于：a.因为多个会话都可见,所以不可以创建多个同名的全局临时表;b.全局临时表是在会话结束时候才会消亡.比如说你在一个存储过程里创建一个全局的临时表,存储过程结束了,但是它不会像局部临时表一样马上消亡.c.如果你想让你每次用SQL的时候总有这么一个的全局临时表一直存在,在里面放些重要的东西.这个时候你就要做到2点:它不属于任何一个会话;它在启动时候自动存在.view plaincopy to clipboardprint?USE master; GO IF OBJECT_ID(dbo.sp_Globals) IS NOT NULL DROP PROC dbo.sp_Globals GO CREATE PROC dbo.sp_Globals AS CREATE TABLE #Globals ( varname sysname NOT NULL PRIMARY KEY, val SQL_VARIANT NULL ); GOUSE master;GOIF OBJECT_ID(dbo.sp_Globals) IS NOT NULL DROP PROC dbo.sp_GlobalsGOCREATE PROC dbo.sp_GlobalsASCREATE TABLE #Globals( varname sysname NOT NULL PRIMARY KEY, val SQL_VARIANT NULL);GO-在执行时候用startup标记该存储过程,SQL每次启动都会调用这个存储过程了就会EXEC dbo.sp_procoption sp_Globals, startup, true;GO- 然后你重启下 SQL Server- 添加数据view plaincopy to clipboardprint?SET NOCOUNT ON; INSERT INTO #Globals VALUES(var1, CAST(abc AS VARCHAR(10); SELECT * FROM #Globals;SET NOCOUNT ON;INSERT INTO #Globals VALUES(var1, CAST(abc AS VARCHAR(10);SELECT * FROM #Globals;-关掉SQL 再开 view plaincopy to clipboardprint?INSERT INTO #Globals VALUES(var2, CAST(abc AS VARCHAR(10); SELECT * FROM #Globals;INSERT INTO #Globals VALUES(var2, CAST(abc AS VARCHAR(10);SELECT * FROM #Globals; -我们可以把这个全局临时表当做一个能够存储全局变量的表.B.表变量1.表变量上是不能创建显示的索引的,只能建PRIMARY KEY和UNIQUE约束;2.声明了表变量后 你无法在修改它的架构.declare s table(a int)alter table s add k int 这种做法是不可取的.所以每次创建都要考虑全面,一击必中.3.大家也许也碰到过表变量是不能做列的限定名称的.select s.a from s 错误 可以给s 取个别名 然后限定.4.表变量在TEMPDB中是确实存在物理的表现形式.view plaincopy to clipboardprint?SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE %#%; /*TABLE_NAME-#Globals*/GO DECLARE T TABLE(col1 INT); INSERT IN