表变量与临时表

临时表:

create table #tmpTbName
(
     colName1 int identity(1,1),
     colName2 nvarchar(20)
)
insert into #tmpTbName(colName1 ,colName2) select c1,c2 from c;
drop table #tmpTbName;--删除临时表

表变量：
declare @tmpTbName table
(
     colName1 int identity(1,1),
     colName2 nvarchar(20)
)

表变量中可以使用的约束: 包括主键约束，唯一约束，Null约束和Check约束(外键约束不能在表变量中使用).

表变量不能做如下事情：

    1.不能赋值给另一个变量。

    2.check约束，默认值，和计算列不能引用自定义函数。

    3.不能为约束命名。

    4.不能Truncate表变量

    5.不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)

存储在系统数据库 tempdb;

断开连接时自动删除临时表;

本地临时表只对当前的会话有效;

临时表不会有日志文件,即对临时表进行的DML等操作也不会形成日志文件。

临时表在会导致存储过程强制被重复编译

1) 临时表的名字不能超过116个字符，这是由于数据库引擎为了辨别不同会话建立不同的临时表，所以会自动在临时表的名字后附加一串

b.当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被DROP

SELECT <select_list> INTO #table”语句创建。你还可以针对临时表使用”INSERT INTO #table EXEC stored_procedure”这样的语句。

临时表可以拥有命名的约束和索引。但是，当两个用户在同一时间调用同一存储过程时，将会产生”There is already an object named ‘<objectname>’ in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名，而使用系统分配的在TempDb中唯一的。

一定建立表变量后就无法对其进行DDL语句操作。因此如果需要为表建立索引或者加一列，你需要临时表。

3) 表变量不能使用select …into语句，而临时表可以

4) 在SQL Server 2008中，你可以将表变量作为参数传入存储过程。但是临时表不行。在SQL Server 2000和2005中表变量也不行。

如果表中的行数非常小，则使用表变量。很多”网络专家”会告诉你100是一个分界线,因为这是统计信息创建查询计划效率高低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进行测试。很多人在自定义函数中使用表变量，如果你需要在表变量中使用主键和唯一索引，你会发现包含数千行的表变量也依然性能卓越。但如果你需要将表变量和其它表进行join,你会发现由于不精准的执行计划，性能往往会非常差。

    为了证明这点，请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了得到足够的测试数据，我将这个表中的数据插入了10遍。然后以ModifiedDate 列作为条件将临时表和表变量与原始的Sales.SalesOrderDetail表进行了Join操作，从统计信息来看IO差别显著。从时间来看表变量做join花了50多秒，而临时表仅仅花了8秒。

    如果你需要在表建立后对表进行DLL操作，那么选择临时表吧。

若允许多个会话可以同时访问某个临时表的话，那么这个临时表就可能会遇到锁的情况。即某个用户会话在对表中地记录进行DML等操作时，为了保证数据的一致性，会对相关的记录进行加锁等措施。当前回话的临时表不用加锁。可以将一些操作在临时表中完成，然后再将最后的结果更新到基本表中。

怎样防止用户重复登录我们的系统？特别是对于银行或是财务部门，更是要限制用户以其工号身份多次登入。

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

上面还涉及到一个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列表: