使用临时表

一、临时表简介

SQL Server临时表和永久表比较类似，但是也有所不同，下面将为您详细介绍SQL Server临时表，供您参考。

SQL Server临时表与永久表相似，只是它的创建是在Tempdb中，它只有在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志，虽它们在Tempdb中体现，是分配在内存中的，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。

SQL Server临时表分为本地和全局两种，本地临时表的名称都是以“#”为前缀，只有在本地当前的用户连接中才是可见的，当用户从实例断开连接时被删除。全局临时表的名称都是以“##”为前缀，创建后对任何用户都是可见的，当所有引用该表的用户断开连接时被删除。

下面我们来看一个创建SQL Server临时表的例子：

CREATE TABLE dbo.#News ( News_id int NOT NULL, NewsTitle varchar(100),

NewsContent varchar(2000), NewsDateTime datetime )

SQL Server临时表可以创建索引，也可以定义统计数据，所以可以用数据定义语言(DDL)的声明来阻止临时表添加的限制，约束，并参照完整性，如主键和外键约束。比如来说，我们现在来为#News表字段NewsDateTime来添加一个默认的GetData()当前日期值，并且为News_id添加一个主键，我们就可以使用下面的语句：　

ALTER TABLE dbo.#News ADD CONSTRAINT [DF_NewsDateTime] DEFAULT (GETDATE()) FOR [NewsDateTime], PRIMARY KEY CLUSTERED ( [News_id] ) ON [PRIMARY] GO SQL Server临时表在创建之后可以修改许多已定义的选项，包括：

1)添加、修改、删除列。例如，列的名称、长度、数据类型、精度、小数位数以及为空性均可进行修改，只是有一些限制而已。

2)可添加或删除主键和外键约束。

3)可添加或删除 UNIQUE 和 CHECK 约束及 DEFAULT 定义(对象)。

4)可使用 IDENTITY 或 ROWGUIDCOL 属性添加或删除标识符列。虽然 ROWGUIDCOL 属性也可添加至现有列或从现有列删除，但是任何时候在表中只能有一列可具有该属性

5)表及表中所选定的列已注册为全文索引。

1、临时表就是用户在创建表的时候添加了“#”前缀的表，其特点是根据进程独立。只有进程的拥有者有表的访问权限，其它用户不能访问该表；
2、不同的用户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独立性。
3、定单和购买意向一般不会保存在真正的“临时表”中，而是实际的普通表，之所以称之为“临时表”，只是一种叫法而已。因为随着一个用户进程的结束，真正的临时表会自动清除，而定单和购买意向数据一般是定时清除，所以一定是保存在普通表中，具备数据的持久性特征（临时表最缺乏的就是数据的持久性）。
4、真正的临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。

事务完毕或会话完毕数据自动清空，不必记得用完后删除数据。
      数据当前会话期可见，其它的会话只能看到其结构，只能看到自己的数据，各会话的数据互不干扰。如静态创建临时表t_tmp(a number primary key,b number)
    用户A可以有数据                 用户B可以有数据
       1 2                          1 2
       2 3                          3 4
       3 4                          6 7
       4 5
A)select count(*) from t_emp; 结果为4(B的数据它看不到)
B)select count(*) from t_emp; 结果为3(A的数据它看不到)
它们有相同的两条记录，但由于数据只作用于当前会话期,所以primary key不会限制它们，只限制当前用户下的A唯一主键。
上述互不干扰特性可以用于处理中间计算过程，如果是常规表在同一时间仅能被一个会话操作，锁定资源拒绝被其它会话访问。

二、使用SQL server临时表解决防止用户重复登录问题

在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。

可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写1，退出时写0，且登录时判断其标志位是否为1，如是则不让该用户工号登录。但是这样那势必会带来新的问题：如发生象断电之类不可预知的现象，系统是非正常退出，无法将标志位置为0，那么下次以该用户工号登录则不可登入，这该怎么办呢？

或许我们可以换一下思路：有什么东西是在connection断开后可以被系统自动回收的呢？对了，SQL Server临时表具备这个特性！但是我们这里的这种情况不能用局部SQL server临时表，因为局部临时表对于每一个connection来说都是一个独立的对象，因此只能用全局临时表来达到我们的目的。

好了，情况已经明朗话了，我们可以写一个象下面这样简单的存储过程:

1. create procedure gp_findtemptable

3. /* 寻找以操作员工号命名的全局临时表

4. * 如无则将out参数置为0并创建该表,如有则将out参数置为1

5. * 在connection断开连接后,全局临时表会被SQL Server自动回收

6. * 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,

7. 但是已经失去活性

8. * 用object_id函数去判断时会认为其不存在.

9. */

10. @v_userid varchar(6), -- 操作员工号

11. @i_out int out -- 输出参数 0:没有登录 1:已经登录

12. as

13. declare @v_sql varchar(100)

14. if object_id(''''tempdb.dbo.##''''+@v_userid) is null

15. begin

16. set @v_sql = ''''create table ##''''+@v_userid+

17. ''''(userid varchar(6))''''

18. exec (@v_sql)

19. set @i_out = 0

20. end

21. else

22. set @i_out = 1

在这个过程中，我们看到如果以用户工号命名的全局临时表不存在时过程会去创建一张并把out参数置为0，如果已经存在则将out参数置为1。

这样，我们在我们的应用程序中调用该过程时，如果取得的out参数为1时，我们可以毫不客气地跳出一个message告诉用户说”对不起，此工号正被使用！”

判断方法范例：

1. select @sTmpWareA="tempdb..[##MARWareA"+ @ComputerName+"]"

2. if exists (select * from tempdb..sysobjects where id = object_id(@sTmpWareA) and type = "U")

3. begin

4. set @sTmpWareA="[##MARWareA"+ @ComputerName+"]"

5. exec( "drop table " +@sTmpWareA )

6. end

7. else

8. set @sTmpWareA="[##MARWareA"+ @ComputerName+"]"

@sTmpWareA 就是SQL Server临时表的名称，过程中使用exec来操作

三、SQL Server临时表的正确删除方式

1、错误的删除操作：

--错误的临时表删除操作，因为所在数据库不同
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[#tempTable]') AND type in (N'U'))
    Begin
    DROP TABLE [dbo].[tempTable]
End
    --错误的临时表删除操作,因为临时表名已变
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[#temptable]'))
Begin
    drop table #temptable
End

2、正确的删除方式：

--正确的临时表删除操作
if object_id('tempdb..#tempTable') is not null Begin
drop table #tempTable
End

四、使用SQL Server临时表来实现字符串合并处理

处理的数据

1. CREATE TABLE tb(col1 varchar(10),col2 int)

2. INSERT tb SELECT 'a',1

3. union ALL SELECT 'a',2

4. union ALL SELECT 'b',1

5. union ALL SELECT 'b',2

6. union ALL SELECT 'b',3

合并处理

1. SELECT col1,col2=CAST(col2 as varchar(100))

2. INTO #t FROM tb

3. ORDER BY col1,col2

4. DECLARE @col1 varchar(10),@col2 varchar(100)

5. UPDATE #t SET

6. @col2=CASE WHEN @col1col1=col1 THEN @col2+','+col2 ELSE col2 END,

7. @col1col1=col1,

8. col2=@col2

9. SELECT * FROM #t

更新处理后的SQL Server临时表

1. col1 col2

2. -

3. a 1

4. a 1,2

5. b 1

6. b 1,2

7. b 1,2,3

8. */

得到最终结果

1. SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1

2. /*结果

3. col1 col2

4. -

5. a 1,2

6. b 1,2,3

7. */

删除测试

1. DROP TABLE tb,#t

2. GO

Ps：

cast:将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
语法
使用 CAST：
CAST ( expression AS data_type )

posted on 2010-09-28 23:22 linzheng 阅读(1367) 评论(1) 编辑收藏举报

刷新页面返回顶部

使用临时表

导航

公告