初识临时表

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

　　创建临时表有多种方法。其一，先创建表结构，跟创建普通表一样，只是表名多了个#号

create table #tmp
(
id int,
name varchar(50)
)

　  有了表结构就可以给表添加数据，添加数据的方法，同普通表一样。

　　其二，创建表同时插入数据 

select *  int #tmp from table_Name  //table_Name 为数据库中的普通表，在创建#tmp的时候自动将table_Name 中的数据填充到#tmp临时表中

　　既然创建了表，我们就会联想到怎么删除表……同样有两种途径。其一，手动删除（drop table #tmp）。其二，当连接断开时，系统自动清除临时表。

　　临时表又可以分为两类：本地和全局。如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该 表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

　　1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。   
　　2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。   
　　3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。  

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

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

 

　　在此，我用临时表做过的例子就是防止用户重复登录

      在我们开发商务软件的时候，常常会遇到这样的一个问题：怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。
　　
　　可能会有人说在用户信息表中加一字段判断用户工号登录的状态，登录后写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。

 

　　上面还涉及到一个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) 存储过程

PG = 计划指南

PK = PRIMARY KEY 约束

R = 规则（旧式，独立）

RF = 复制筛选过程

S = 系统基表

SN = 同义词

SQ = 服务队列

TA = 程序集 (CLR) DML 触发器

TF = SQL 表值函数

TR = SQL DML 触发器

U = 表（用户定义类型）

UQ = UNIQUE 约束

V = 视图

X = 扩展存储过程