表变量,临时表
9.2 表变量、临时表
临时表分为局部临时表和全局临时表，分别以#以及##为表名前缀。局部临时表在会话间不能共享，在会话结束后，临时表会被删除；全局临时表在会话间是可共享的，当创建的会话关闭时，全局临时表也会被删除。
表变量，发现过许多误解，以为表变量是内存表，是存储在内存中的，其实表变量与临时表一样，都是存储在Tempdb中的，表变量是在SQL Server 2000时加入的，当初是为了解决临时表造成重编译的问题而添加的。做个简单的测试，如下面的语句代码清单9-1中所示，可以获取当前会话使用Tempdb的空间数量，新建一个会话1，首先执行一次查询，发现user_objects_alloc_page_count字段对应的值是0。创建一个临时表，并插入一条数据。如图9-1中所示，使用了1个数据页。
SELECT *
FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)
WHERE ddssu.session_id=@@SPID
GO
CREATE TABLE #tempSpace1(c1 CHAR(8000))
GO
INSERT INTO #tempSpace1 (c1)
VALUES ('a')
GO
SELECT *
FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)
WHERE ddssu.session_id=@@SPID
GO
代码清单9-1 查看临时表Tempdb使用情况
图9-1 查看临时表Tempdb使用结果
相同的，创建一个具有相同类型字段的表变量，查看会话对表空间的作用情况，如图9-2中所示，也使用了一个数据页。表变量确实使用了Tempdb作为存储。既然两者都是存储在Tempdb中的，那么为何还会有两种类型的数据呢，对比一下图9-1与图9-2中所显示的user_objects_dealloc_page_count字段（该字段表示回收的用户空间的数据页个数），会发现，图9-1中字段的值是0，而图9-2中的值是1，也就是说，表变量已经被回收了。它们的区别，第一个，就是作用域不同。表变量是批处理级的，当批处理结束后，表变量就会被回收，而临时表是会话级的，只能显式地删除，或者会话关闭才会回收。
SELECT *
FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)
WHERE ddssu.session_id=@@SPID
GO
DECLARE @tempSpace TABLE(c1 CHAR(8000))
INSERT INTO @tempSpace (c1)
VALUES ('a')
GO
SELECT *
FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)
WHERE ddssu.session_id=@@SPID
GO
SELECT *
FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)
WHERE ddssu.session_id=@@SPID
GO
代码清单9-2 查看表变量Tempdb使用情况
图9-2 查看表变量Tempdb使用结果
表变量与临时表，存在着几个较为明显的差异，前面提到了，表变量是为了处理临时表造成的执行计划频繁重新编译而引进的新的功能。接下来，研究一下，表变量如何避免重编译。
9.2.1 统计信息
表变量与临时最大的差异，同时也需要极为注意的是表变量是，没有办法创建统计信息。所以，如前面章节中所介绍的，查询优化器在分析执行计划的时候，对表变量使用"猜测"的方式来分析。这其中的好处就是，没有统计信息的更新，就不会造成执行计划的重编译的问题。
使用下面的语句（直接点击XML格式的数据行，可以打开执行计划的可视化界面），新建临时表，并插入200条数据，执行查询，并查看其执行计划关于表操作的详细信息，如图9-3中所示，在临时表的预估数据行一行信息中，是200条，它的统计信息是准确的。
CREATE TABLE #tempTable1(c1 CHAR(8000));
DECLARE @cnt INT=0;
WHILE(@cnt<200)
BEGIN
INSERT INTO #tempTable1 (c1)VALUES('a');
SET @cnt+=1;
END;
SET STATISTICS XML ON
SELECT *
FROM #tempTable1 AS ts
SET STATISTICS XML OFF
代码清单9-3 临时表统计信息
图9-3 临时表统计信息
下面的语句，可以查看表变量的详细情况，如图9-4中，预估行的数量只有1。对于SQL Server来说，表变量是没有统计信息的，所以SQL Server只能用"猜测"的方式来预估表变量的数据行，SQL Server会认为表变量都是小表，执行计划通常也只会用NESTED LOOP来处理表变量的联接查询。因此，在使用表变量的时候，建议只应用在小数据量的查询中。
DECLARE @tempTable1 TABLE(c1 CHAR(8000));
DECLARE @cnt INT=0;
WHILE(@cnt<200)
BEGIN
INSERT INTO @tempTable1 (c1)VALUES ('a');
SET @cnt+=1;
END;
SET STATISTICS XML ON
SELECT * FROM @tempTable1 AS ts;
SET STATISTICS XML OFF
代码清单9-4 表变量统计信息
图9-4 表变量统计信息
9.2.2 索引
临时表的索引与普通物理的索引相同，可以在创建表的时候指定主键和唯一约束，同时也可以在表创建以后再添加、修改或删除其他索引。而表变量只能在定义变量的时候指定主键或唯一约束，表变量在声明以后，便不能再添加或删除任何索引。
9.2.3 表结构修改
临时表与普通物理表相同，在创建后，可以再增减字段，修改字段属性，增加或删除约束等。而表变量在声明后，便不能再做任何关于表结构的修改。这也是为了重编译而考虑的，因为在当初（SQL Server早期版本设计中）的设计中，表结构的变更是会引起执行计划的重编译的。
