学步园

-- 对于表变量和临时表的例子：
-- 约束(Constraint) 索引(Index) I/O 开销 作用域(SCOPE)存储位置 其他

/* **************************************
A) 约束(Constraint) : 在临时表和表变量，都可以创建Constraint ，针对表变量，只有定义时能加 CONSTRAINT

******************************************* */
USE tempdb
GO
IF OBJECT_ID('TEMPDB..#1') IS NOT NULL
    DROP TABLE dbo.#1
GO

CREATE TABLE #1
(
    ID INT ,
    Nr NVARCHAR(50) NOT NULL,
    OperationTime DATETIME DEFAULT (GETDATE()),
    CONSTRAINT pk_#1_id PRIMARY KEY (ID)
)

ALTER TABLE #1 ADD CONSTRAINT CK_#1_Nr CHECK(Nr BETWEEN '10001' AND '1999')


/*    上面脚本 可以看出临时表#1在创建时，创建了Constraint , 也可以看出创建临时表#1后创建Constraint，
    下面来为表变量的场景，在定义表变量时不能指定Constaint名，定义表变量后不能对表变量创建Constraint    */

--EG:
USE tempdb
GO
DECLARE @1 TABLE 
(
    ID INT,
    Nr NVARCHAR(50) NOT NULL,
    OperationTime DATETIME DEFAULT (GETDATE()),
    CONSTRAINT [PK_@1_ID] PRIMARY KEY(ID)
)
/*    报错:消息 156，级别 15，状态 2，第 6 行
    关键字 'CONSTRAINT' 附近有语法错误。    */

USE tempdb
GO
DECLARE @1 TABLE 
(
    ID INT,
    Nr NVARCHAR(50) NOT NULL,
    OperationTime DATETIME DEFAULT (GETDATE())
)
ALTER TABLE @1 ADD CONSTRAINT [CK_@1_Nr] CHECK (Nr BETWEEN '10001' AND '19999')

--    报错:消息 102，级别 15，状态 1，第 7 行
--  '@1' 附近有语法错误。

/*
在上述代码中发现，在解析T-SQL语句过程就发生错误，也就是说SQL SERVER不支持定义表变量时对Constraint命名，也不支持定义表变量后，
对其建Constraint命名,也不支持定义表变量后,对其建Constraint。 

------------------------------------------------------------------------------------------------------

在MSSSMS中我们先执行前创建临时表#1，不关闭当前会话的情况下，另建一个查询，执行也其相同的代码。便会报出以下错误：
    消息 2714，级别 16，状态 4，第 1 行
    数据库中已存在名为 'pk_#1_id' 的对象。
    消息 1750，级别 16，状态 0，第 1 行
    无法创建约束。请参阅前面的错误消息。
而当我们执行表变量的@1则不会报错，说明表变量也不需要进行DROP Table的操作，一次执行完成后就会消失。


我们发现在创建临时表 #1 的过程，明确给了一个主键名称 'PK_#1_ID'，当右边再创建相同临时表#1的时候就发生了对象重复错误问题。我们也可以通过SQL        SERVER 提供的系统视图 SYS.OBJECTS 查询约束'PK_#1_ID'的信息        
            
*/

USE tempdb
GO

SELECT * FROM sys.objects WHERE name = 'pk_#1_id'
/*
在系统视图 sys.objects 发现'pk_#1_id'名称后面不加任何的随机数值表述不同会话有不同的对象。
根据 SQL SERVER 对 sys.objects 的描述规则， sys.objects 中的 name 列数据是唯一的。当另一个会话创建相同的对象时就会发生对象重复的错误。

在Constraint中， Foreign Key 是不能应用与表变量，对于临时表，创建 Foreign Key 是没有意义的。也就是说临时表不受 Foreign Key 约束。
下面通过例子来说明临时表的情况。
*/
USE tempdb
GO

IF OBJECT_ID('tempdb..#1') is not null
    DROP TABLE #1
GO

IF OBJECT_ID('TEMPDB..#2') IS NOT NULL
    DROP TABLE #2
GO

CREATE TABLE #1
(
id int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint PK_#1_ID primary key(ID)
)

ALTER TABLE #1 ADD CONSTRAINT CK_#1_Nr check (Nr between '10001' and '19999')

CREATE TABLE #2(
    ID INT PRIMARY KEY ,
    FOREIGNID INT NOT NULL,
    FOREIGN KEY(FOREIGNID) REFERENCES #1(ID)
)
GO


/*    不为临时表定义 FOREIGN KEY 约束 '#2'。无论是局部临时表还是全局临时表，都不会对它们强制使用 FOREIGN KEY 约束。
    可以看出对于临时表不强制 FOREIGN KEY 约束,我们也可以通过 SQL SERVER 系统视图 sys.foreign_keys 查询        */

use tempdb
go
select * from sys.tables where name like '#[1-2]%'
select * from sys.foreign_keys

/*    上面的查询,只能看到 sys.tables 表中存在刚才创建的临时表 #1 和 #2，在sys.foreign_keys 看不到有关 foreign_key约束信息。
    这也验证了左边 SQL SERVER 提示的，在临时表中无强制使用 FOREIGN KEY 约束。 */
  
 /* **************************************
B) 索引(INDEX) : 从索引方面看临时表和表变量,与从Constraint上分析有些类似，在临时表中，它与真实表一样可以创建索引。在表变量定义过程中，也可以创建一些类似唯一和聚集索引
**************************************** */
--EG:
USE tempdb
GO

DECLARE @1 TABLE
(
    ID INT PRIMARY KEY CLUSTERED ,
    Nr nvarchar(50) UNIQUE Nonclustered
)

INSERT INTO @1(ID , Nr ) VALUES( 1 , '10001');
INSERT INTO @1(ID , Nr ) VALUES( 2 , '10002');
INSERT INTO @1(ID , Nr ) VALUES( 8 , '10003');
INSERT INTO @1(ID , Nr ) VALUES( 7 , '10004');
INSERT INTO @1(ID , Nr ) VALUES( 3 , '10005');
--INSERT INTO @1(ID , Nr ) VALUES( 1 , '10001');
SELECT  * 
FROM SYS.indexes AS A  INNER JOIN SYS.tables AS B ON B.object_id = A.object_id
ORDER BY B.create_date DESC

SELECT Nr FROM @1 WHERE Nr = '10005'

/*
第一个查询在表变量中使用聚集PRIMARY KEY，创建非聚集的Unique约束
第二个查询，看查询计划应用到在变量创建的唯一索引'UN_#...'
--------------------------------------------------------------

下面是临时表索引的例子，我们拿一个例子说明，与前边说的Constraint例子有点相似，
这里我们对临时表创建索引，并给索引一个具体名称，测试是否会重复。
*/
-- EG:
USE tempdb
GO

IF OBJECT_ID('#1') IS NOT NULL
    DROP TABLE #1
GO

CREATE TABLE #1
(
    ID INT PRIMARY KEY ,
    Nr NVARCHAR(50) NOT NULL ,
    OperationTime DATETIME DEFAULT(GETDATE())
)

CREATE NONCLUSTERED INDEX IX_#1_Nr ON #1(Nr ASC)
GO

SELECT B.NAME AS TABLENAME , A.object_id , a.name , a.index_id , a.type 
FROM SYS.indexes AS A INNER JOIN SYS.TABLES AS B ON A.OBJECT_ID = B.OBJECT_ID
WHERE B.NAME LIKE '#1[_]%'
ORDER BY B.CREATE_DATE ASC

/*
从返回的结果,我们看到在系统视图表SYS.INDEXS中，创建有两个相同的索引 'IX_#1_Nr', 但注意下object_id 数据不同。在sql server 中允许不同的表索引名称可以相同的。在并发的环境下，按原理是可以对临时表创建的索引给明确名称的。除非并发的情况会发生重复的表名或者重复的Constraint， 或其它系统资源不足的问题，才会导致出错。
*/

  
 /* **************************************
C) I/O开销  : 临时表与表变量，在I/O开销的描述，我们直接通过一个特殊的例子去描述它们，在SSMS上新增加两个查询，分别输入临时表和表变量的测试代码：
**************************************** */
-- EG: 临时表：
USE tempdb
GO
IF OBJECT_ID('#1') IS NOT NULL
    DROP TABLE #1
GO

CREATE TABLE #1
(
    ID INT PRIMARY KEY ,
    Nr NVARCHAR(50) NOT NULL ,
    OperationTime DATETIME DEFAULT(GETDATE())
)

INSERT INTO #1( ID, Nr )
SELECT TOP 5000 ROW_NUMBER() OVER(ORDER BY A.OBJECT_ID) ,
LEFT(A.NAME + B.NAME , 50) 
FROM master.SYS.all_objects AS A ,SYS.all_columns AS B 
WHERE TYPE = 'S'

SELECT Nr , COUNT(Nr) AS Sum_
FROM #1
WHERE Nr LIKE 'sysrscolss%'
GROUP BY Nr
------------------------------------------------------
-- EG: 表变量
USE tempdb
GO

DECLARE @1 TABLE
(
ID INT PRIMARY KEY ,
Nr NVARCHAR(50) NOT NULL ,
OperationTime DATETIME DEFAULT(GETDATE())
)

INSERT INTO @1( ID, Nr , OperationTime)
SELECT TOP 5000 ROW_NUMBER() OVER(ORDER BY a.object_id) ,
    LEFT(a.name + b.name ,50) , a.create_date
FROM master.sys.all_objects AS a , master.sys.all_columns AS b 
WHERE type = 'S'

SELECT Nr , COUNT(Nr) AS Sum_
FROM @1
WHERE Nr LIKE 'sysrscolss%'
GROUP BY Nr

/*
通过上面两个查询，查看运行的I/O图形描述，可以看出来查询开始，不管是临时表还是表变量，都使用到了聚集索引扫描，两者虽然返回的数据一致，
但 I/O 的开销不同：临时表的 I/O 开销是0.0342361，而表变量只有0.003125，相差非常大。在临时表的执行计划图形中，我们发现一行 '缺少索引(影响48.3374); create nonclustered index ...'提示信息.我们对临时表#1,在字段'Nr'上创建一个非聚集索引，再看执行计划结果：

*/

CREATE NONCLUSTERED INDEX  IX_#1_Nr ON #1(Nr)

/*
我们在临时表 #1 上创建完索引 'IX_#1_Nr'，再运行看执行计划。在临时表 #1 查询时用了索引查找(index seek) ,而且I/O开销减少到了0.0053472 。虽然开始查询的
这次的开销为0.0053472。虽然开始查询的时候I/O开销还是比表变量开始查询的时候大一些，但执行步骤中比变量少了一个排序(SORT) 开销，最后的看SELECT结果，估计子树的成本比使用表变量的大大减少。
这里的例子只是描述一个特殊的情况，在真实的环境中，要根据实际的数据量来判断是否使用临时表或者表变量。倘若在存储过程中，当数据量非常少如只有不到50行记录，数据占的页面也不会超过1个页面，那么使用表变量是一个很好的解决方案。
*/

 /* **************************************
D) 作用域（SCOPE）: 表变量像局部变量(local variable) 一样，有着很窄的作用域，只能应用于定义的函数、存储过程或者批处理内。 如:一个会话里面有几个批处理，那么表变量只能作用在它定义所在的批处理范围内。其它的批处理无法再调用它。
**************************************** */
USE tempdb
GO

SET NOCOUNT ON 
DECLARE @1 TABLE(
ID INT PRIMARY KEY CLUSTERED ,
Nr nvarchar(50) UNIQUE NONCLUSTERED 
)

INSERT INTO @1 (ID , NR ) VALUES( 1,'10001');
INSERT INTO @1 (ID , NR ) VALUES( 2,'10002');
INSERT INTO @1 (ID , NR ) VALUES( 8,'10003');
INSERT INTO @1 (ID , NR ) VALUES( 3,'10004');
INSERT INTO @1 (ID , NR ) VALUES( 7,'10005');

SELECT * FROM @1  
GO    --批处理结束点

SELECT * FROM @1
-- 消息 1087，级别 15，状态 2，第 2 行
-- 必须声明表变量 "@1"。
/*
上面的查询相当于一个会话,'GO'描述的一个批处理的结束点,在'GO'之前定义的表变量,
在'GO'之后调用是发生'必须声明变量@1'的错误提示.
临时表和表变量不同,临时表的作用域是当前会话都有效,一直到会话结束或者临时表被DROP的时候,也就是说可以跨当前会话的几个批处理范围.
*/

SELECT * FROM #1

USE tempdb
GO
IF OBJECT_ID('TEMPDB..#1') IS NOT NULL
    DROP TABLE #1
GO

CREATE TABLE #1 (
    ID INT ,
    OperationTime DATETIME DEFAULT(GETDATE()),
    CONSTRAINT PK_#1_id PRIMARY KEY (id)
)

SELECT * FROM #1
GO  --批处理结束点

SELECT * FROM #1

/*
上面可以看出在GO前后都可以查询到临时表 #1 .
在描述临时表与表变量的作用域时,有个地方要注意的是,当 SP_EXECUTESQL 或EXECUTE 语句执行字符串时,字符串将作为它的自包含批处理执行.如果表变量在 SP_EXECUTESQL 或EXECUTE 语句之前定义,在 SP_EXECUTESQL 或EXECUTE 语句的字符串中无法调用外部定义的表变量.
*/
--EG:
USE tempdb
GO

SET NOCOUNT ON 
DECLARE @1 TABLE(
ID INT PRIMARY KEY CLUSTERED ,
Nr nvarchar(50) UNIQUE NONCLUSTERED 
)

INSERT INTO @1 (ID , NR ) VALUES( 1,'10001');
INSERT INTO @1 (ID , NR ) VALUES( 2,'10002');
INSERT INTO @1 (ID , NR ) VALUES( 8,'10003');
INSERT INTO @1 (ID , NR ) VALUES( 3,'10004');
INSERT INTO @1 (ID , NR ) VALUES( 7,'10005');

SELECT * FROM @1

EXECUTE (N'SELECT * FROM @1')
GO
/* 上述脚本,当执行到 EXECUTE (N'SELECT * FROM @1') 时候,同样发生与之前单独执行查询语句一样的错误,提示: '必须声音变量@1'.
临时表是可以在 SP_EXECUTESQL 或EXECUTE 语句执行字符串中被调用.
如:EXECUTE (N'SELECT * FROM #1')  */



-------******************* --------------


/* **************************************
E) 其它: 临时表与表变量,还有其它的特征,如临时表受事务回滚,而表变量不受事务回滚影响.对应事务方面,更为正确的说法是表变量的事务只在表变量更新期间存在.因此减少了表变量对锁定和记录资源的需求.
**************************************** */
USE tempdb
GO
SET NOCOUNT ON 

IF OBJECT_ID('#1') IS NOT NULL
    DROP TABLE dbo.#1
GO
CREATE TABLE #1(ID INT, Nr NVARCHAR(50))
DECLARE @1 TABLE(id INT, Nr NVARCHAR(50))

BEGIN TRAN /* 事务开始 */

INSERT INTO #1(ID ,Nr) 
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY A.OBJECT_ID) ,
    LEFT(A.name + B.name , 50 )
FROM SYS.all_objects AS A ,SYS.all_columns AS B 

INSERT INTO @1(ID ,Nr) 
SELECT TOP 1 ROW_NUMBER() OVER(ORDER BY A.OBJECT_ID) ,
    LEFT(A.name + B.name , 50 )
FROM SYS.all_objects AS A ,SYS.all_columns AS B 

ROLLBACK TRAN /* 回滚事务 */

SELECT * FROM #1
SELECT * FROM @1
go

/*
这里发现'ROLLBACK TRAN'之后，临时表 #1 没有数据插入，而表变量@1还有一条数据存在。说明表变量不受'Rollback Tran' 所影响，它的行为有类似于局部变量一样。
另外 SQL SERVER对表变量不保留任何的统计信息，因为如此，我们在数据量大的时候使用表变量，发现比临时表慢许多。
*/

参考：
http://blog.csdn.net/gulijiang2008/article/details/6091802
http://www.jb51.net/article/28788.htm
http://support.microsoft.com/kb/305977/zh-cn
http://www.51testing.com/html/78/n-816578.html