-- 对于表变量和临时表的例子：-- 约束(Constraint) 索引(Index) I/O 开销 作用域(SCOPE)存储位置 其他/* **************************************A) 约束(Constraint) : 在临时表和表变量，都可以创建Constraint ，针对表变量，只有定义时能加 CONSTRAINT******************************************* */USE tempdbGOIF OBJECT_ID('TEMPDB..#1') IS NOT NULL DROP TABLE dbo.#1GOCREATE 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 tempdbGODECLARE @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 tempdbGODECLARE @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 tempdbGOSELECT * 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 tempdbGOIF OBJECT_ID('tempdb..#1') is not null DROP TABLE #1GOIF OBJECT_ID('TEMPDB..#2') IS NOT NULL DROP TABLE #2GOCREATE 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 tempdbgoselect * 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 tempdbGODECLARE @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_idORDER BY B.create_date DESCSELECT Nr FROM @1 WHERE Nr = '10005'/*第一个查询在表变量中使用聚集PRIMARY KEY，创建非聚集的Unique约束第二个查询，看查询计划应用到在变量创建的唯一索引'UN_#...'--------------------------------------------------------------下面是临时表索引的例子，我们拿一个例子说明，与前边说的Constraint例子有点相似，这里我们对临时表创建索引，并给索引一个具体名称，测试是否会重复。*/-- EG:USE tempdbGOIF OBJECT_ID('#1') IS NOT NULL DROP TABLE #1GOCREATE 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)GOSELECT 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_IDWHERE 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 tempdbGOIF OBJECT_ID('#1') IS NOT NULL DROP TABLE #1GOCREATE 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 #1WHERE Nr LIKE 'sysrscolss%'GROUP BY Nr-------------------------------------------------------- EG: 表变量USE tempdbGODECLARE @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_dateFROM master.sys.all_objects AS a , master.sys.all_columns AS b WHERE type = 'S'SELECT Nr , COUNT(Nr) AS Sum_FROM @1WHERE 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 tempdbGOSET 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 #1USE tempdbGOIF OBJECT_ID('TEMPDB..#1') IS NOT NULL DROP TABLE #1GOCREATE TABLE #1 ( ID INT , OperationTime DATETIME DEFAULT(GETDATE()), CONSTRAINT PK_#1_id PRIMARY KEY (id))SELECT * FROM #1GO --批处理结束点SELECT * FROM #1/*上面可以看出在GO前后都可以查询到临时表 #1 .在描述临时表与表变量的作用域时,有个地方要注意的是,当 SP_EXECUTESQL 或EXECUTE 语句执行字符串时,字符串将作为它的自包含批处理执行.如果表变量在 SP_EXECUTESQL 或EXECUTE 语句之前定义,在 SP_EXECUTESQL 或EXECUTE 语句的字符串中无法调用外部定义的表变量.*/--EG:USE tempdbGOSET 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 @1EXECUTE (N'SELECT * FROM @1')GO/* 上述脚本,当执行到 EXECUTE (N'SELECT * FROM @1') 时候,同样发生与之前单独执行查询语句一样的错误,提示: '必须声音变量@1'.临时表是可以在 SP_EXECUTESQL 或EXECUTE 语句执行字符串中被调用.如:EXECUTE (N'SELECT * FROM #1') */-------******************* --------------/* **************************************E) 其它: 临时表与表变量,还有其它的特征,如临时表受事务回滚,而表变量不受事务回滚影响.对应事务方面,更为正确的说法是表变量的事务只在表变量更新期间存在.因此减少了表变量对锁定和记录资源的需求.**************************************** */USE tempdbGOSET NOCOUNT ON IF OBJECT_ID('#1') IS NOT NULL DROP TABLE dbo.#1GOCREATE 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 #1SELECT * FROM @1go/*这里发现'ROLLBACK TRAN'之后，临时表 #1 没有数据插入，而表变量@1还有一条数据存在。说明表变量不受'Rollback Tran' 所影响，它的行为有类似于局部变量一样。另外 SQL SERVER对表变量不保留任何的统计信息，因为如此，我们在数据量大的时候使用表变量，发现比临时表慢许多。*/