-- 对于表变量和临时表的例子：
-- 约束(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对表变量不保留任何的统计信息，因为如此，我们在数据量大的时候使用表变量，发现比临时表慢许多。
*/
