SQL Server 临时表和表变量系列之认知误区篇

摘要

关于临时表和表变量，是一个老生常谈的话题，但是，我相信很多SQL Server老司机都存在或多或少的认知误区。指出一些常见的认知误区就是写作本文的目的，希望以此来找到一些常常被我们忽略的地方。

认知误区

SQL Server关于临时表和表变量的常见的认知误区包含以下六点：
表变量不支持事务
表变量不能创建索引
表变量没有统计信息
表变量存驻留在内存中
表变量访问比临时表快
局部临时表不需要手动回收资源

表变量不支持事务

从我们前一篇文章SQL Server 临时表和变量系列之对比篇中的“对事务支持”部分的测验来看，的确表变量是不支持用户事务回滚的。但是，以此得出表变量不支持事务这种说法是错误的。原因是：表变量的确不支持用户显示事务回滚，即有BEGIN TRANSACTION类型的事务回滚，但是表变量还是支持DML语言操作的事务性的。
怎么理解呢？就是说，表变量数据操作的时候，是不可能存在一些数据成功，一些数据失败的。比如：在一个批处理中，向表变量中插入两条记录，不可能存在只有一条记录插入成功，而另一条记录插入失败的情况；只可能是两条数据都插入成功或者都不成功的情况。这就是表变量支持事务原子性的一种表现。

--Table variable support transaction
-- declare temp variable
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO @tb_table
SELECT NEWID() UNION ALL SELECT NEWID();

--END

表变量不能创建索引

表变量本质上是一种特殊的变量类型，只不过它具有了很多表的属性。比如：字段、字段数据类型、字段宽度、唯一约束、NULL、NOT NULL约束、CHECK和DEFAULT约束等。由于表变量定义完毕以后，不支持对表变量结构的任何变更和索引创建，所以很多人会认为表变量不能创建索引，比如这篇文章SQL Server Temp Table vs Table Variable Performance Testing中的这句话“However, when we query rows using the indexed column of the temporary table, which is not indexed in the table variable since this is not available for table variables”就认为表变量无法创建索引，这个观点的错误，导致了整个测试结论是错误的。其实，表变量完全可以在定义的时候就直接创建索引，参见如下代码，在定义表变量时候创建了非聚集索引IX_Indate:

--Table variable support Index
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);
--END

表变量没有统计信息

在关系型数据库RDBMS系统中，统计信息是执行计划正确评估的基础，和SQL执行效率息息相关，对SQL语句的查询性能起着至关重要的作用。关于SQL Server统计信息的详情，请参见之前的文章SQL Server幕后英雄 － 统计信息。那么，表变量到底有没有统计信息呢？在过往的经验中，很多SQL Server数据库的同行都认为表变量没有统计信息存在，其实这种认识是错误的。我们可以参加下面的例子：

--Table variable has statistics
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

DECLARE
    @table_variable_id bigint
    ;
SELECT TOP 1 @table_variable_id = object_id
FROM tempdb.sys.all_objects AS A
WHERE parent_object_id = 0
ORDER BY create_date DESC

SELECT  
    statistics_name = st.name
    ,table_name = OBJECT_NAME(st.object_id)
    ,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    sys.stats AS st WITH(NOLOCK) 
        INNER JOIN sys.stats_columns AS stc WITH(NOLOCK)
            ON st.object_id = stc.object_id  
            AND st.stats_id = stc.stats_id 
WHERE st.object_id = @table_variable_id
--END

执行完毕后，表变量的统计信息展示如下：

表变量仅驻留在内存中

我在一些图书或者网络上的文章经常看到有人讲，表变量的数据是驻留在内存中的。这个观点在大部分情况下是正确的，但是当SQL Server认为数据库内存不足时（不是物理机内存不足，是SQL OS的内存不足），表变量的数据是会写入到Tempdb的数据文件，即写入了磁盘文件中。
我们可以参照下面的思路来证明SQL Server内存不足时，表变量的数据会写入tempdb数据文件：
修改Max Server Memory为512MB
重启SQL Service使得配置生效
获取测试前的内存使用量和Tempdb数据文件大小
向表变量中存入10000条记录（大概会占据780MB空间，超过512MB的最大内存大小）
再次获取测试后的内存使用量和Tempdb数据文件大小
代码如下：

USE tempdb
GO

--table variable just saved data into memory

-- change configuration of Max Server Memory to 512MB.
EXEC sys.sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'max server memory (MB)', 512
GO
RECONFIGURE WITH OVERRIDE
GO

-- restart SQL Server serivces

-- if you didn't restart sql service, clean the buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS

-- Check memory used before testing
SELECT MemoryUsedBefore = count(1) / 128.
FROM sys.dm_os_buffer_descriptors b 
WHERE is_modified = 1

-- Check disk space used before testing
SELECT
        [DBName] = db_name()
        , [LogName] = s.name
        , [SpaceUsedBefore] = CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8)/1024
FROM sys.database_files AS s

-- table variable data inserting area
DECLARE  @saveToDisk TABLE(bigChar char(8000));
DECLARE
    @do int = 1; 
WHILE @do <= 100000
BEGIN
    INSERT INTO @saveToDisk VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END

-- Check memory used after testing
SELECT MemoryUsedAfter = count(1) / 128.
FROM sys.dm_os_buffer_descriptors b 
WHERE is_modified = 1

-- Check disk space used after testing
SELECT
        [DBName] = db_name()
        , [LogName] = s.name
        , [SpaceUsedAfter] = CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8)/1024
FROM sys.database_files AS s

执行结果截图如下：

分析结果：
从结果来看Tempdb数据文件从2.4MB增长到783.7MB，大约增加了780MB，这个数据增长量和表变量需要暂存的数据量大小是非常相近的；内存大小从0.3MB增长到250.6MB，大约增长了250MB，接近SQL Server Max Server Memory的一半。这个现象已经可以说明当SQL Server最大内存大小不足以存放表变量的数据大小的时候，SQL Server会使用tempdb数据文件来暂存表变量的数据；还可以推测，当表变量存放数据接近SQL Server Max Server Memory一半空间的时候，就会将表变量数据写入磁盘。
从Windows资源管理器层面我们也可以发现sqlservr进程有大量写入Tempdb数据文件的操作。如下截图，在执行表变量插入操作的时候，Disk Write速度接近了100MB/Sec，平时的速度不到100KB/Sec：

表变量访问比临时表快

由之前关于临时表和表变量系列文章SQL Server 临时表和变量系列之对比篇中“数据存储”部分结论，我们知道表变量的数据是存放在SQL Server的内存缓存中，而临时表是存放在Tempdb数据文件的磁盘上，因此很多人会得出结论，使用表变量比临时表效率更高，性能更好。这种认知也是不够全面和错误的，理由是表变量数据也有可能存在磁盘中：
本文的前一小节“表变量仅驻留在内存中”，我们已经使用实际例子证明了，当SQL Server内存大小不足以存放表变量数据大小的时候，SQL Server会将表变量的数据全部存放在Tempdb的数据文件磁盘上，以此来避免因为内存不足而导致服务死掉。换句话说，当这种情况出现的时候（当然这种场景在现在大内存数据库时代已经非常少见了），表变量和临时表都是将数据暂存在磁盘上，数据存取都必须有物理磁盘的读写操作，无所谓谁快谁慢。基于大多数人习惯于不给表变量创建索引，这种场景反而会导致表变量的查询比临时表慢，性能更低。

局部临时表不需要手动回收资源

由之前关于临时表和表变量系列文章SQL Server 临时表和变量系列之对比篇中“析构方式”部分，我们知道局部临时表在当前进程退出的时候，SQL Server系统会自动析构局部临时表对象。因此，很多人认为我们没必要再手动回收临时表资源。
但是，我个人还是强烈建议手动回收临时表资源，又特别是临时表中暂存了大量数据的时候（比如超过10万条记录数），使用TRUNCATE TABLE不记录日志的方式删除临时表所有数据，然后再手动删除临时表结构。以防止临时表直接DROP操作给tempdb日志文件带来写入压力。

IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE #tb_table
    DROP TABLE #tb_table
END

写在最后

这篇文章主要谈论了人们常见的关于临时表和表变量的认知误区，希望能带给大家关于两者的正确认识，也为下一篇文章“SQL Server 临时表和表变量系列之选择篇”打下基础。