SQLSERVER 中表变量与临时表

SQL SERVER 中的变量表与临时表

1.临时表

    临时表和永久表类似，很多永久表具有的功能特性，临时表都具有，临时表它的创建是在tempdb中，它只有在一个数据库连接结束后或者由sql命名drop掉，才消失，否则他会一直存在，临时表的创建的时候会产生sql server 系统日志，

临时表分为两种：

                      一.以#开头的本地临时表，特点：只有在本地当前用户连接中才是可见的，当用户从实例中断开连接后自动被删除，

                      二.以##开头的全局临时表，特点：创建后，任何用户都可见，当所有用户断开连接后，被删除。

--本地临时表的创建
CREATE TABLE #LOCALTABLE
(
 ID INT 
)

--全局临时表
CREATE TABLE ##GOBAL(
ID INT 
)

特别注意，存储过程中的临时表，在存储过程结束后，就会被自动删除(若果是嵌套在游标中的话，就不会删除)

CREATE PROC PROC_A
AS

   CREATE TABLE #SHOW(
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    VAL VARCHAR(100) 
   )
   
   INSERT INTO #SHOW VALUES('A')
   INSERT INTO #SHOW VALUES('B')
   INSERT INTO #SHOW VALUES('C')
   SELECT * FROM #SHOW
 
 GO
 
 EXEC PROC_A
 --当存储过程执行完毕之后，就自动的删除了； 若果是嵌套在游标中的话，就不会删除)
SELECT * FROM #SHOW  --消息 208，级别 16，状态 0，第 1 行  对象名 '#SHOW' 无效。

演示约束条件的添加

 --创建一个临时表
 CREATE TABLE #SHOW(
  ID INT NOT NULL,
  TITLE VARCHAR(100),
  CONTENT VARCHAR(300),
  DATETIMES DATETIME
 )
 
 --添加约束；
 ALTER TABLE #SHOW
 ADD
    CONSTRAINT CONSTRAINT_NAME DEFAULT(GETDATE()) FOR DATETIMES,
    PRIMARY KEY CLUSTERED(ID)
    GO
 

临时表在创建之后可以修改许多已定义的选项，包括： 

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

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

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

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

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

2.表变量

       它的申明是使用DECLARE,表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量，像我们常用到的，如 @@Error代表错误的号，@@RowCount代表影响的行数。 

 DECLARE @LOCALTB TABLE
 (
   ID INT 
 )
 GO
 --全局
 DECLARE @@GOBAL TABLE
 (
   ID INT 
 )

      1.表变量是存储在内存中的，当用户在访问表变量的时候，SQL Server是不产生日志的，而在临时表中是产生日志的; 

　　2.在表变量中，是不允许有非聚集索引的; 

　　3.表变量是不允许有DEFAULT默认值，也不允许有约束; 

　　4.临时表上的统计信息是健全而可靠的，但是表变量上的统计信息是不可靠的; 

　　5.临时表中是有锁的机制，而表变量中就没有锁的机制。 

总结：

        1使用表变量主要需要考虑的就是应用程序对内存的压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。我们对于较小的数据或者是通过计算出来的推荐使用表变量。如果数据的结果比较大，在代码中用于临时计算，在选取的时候没有什么分组的聚合，就可以考虑使用表变量。 

　　2一般对于大的数据结果，或者因为统计出来的数据为了便于更好的优化，我们就推荐使用临时表，同时还可以创建索引，由于临时表是存放在Tempdb中，一般默认分配的空间很少，需要对tempdb进行调优，增大其存储的空间。

ps：推荐一篇写的比较全的文章

http://www.jb51.net/article/28788.htm