摘要

在SQL Server代码编写过程中，经常会有需要临时“暂存”一部分数据结果集，供上下文使用，这个时候，我们有两种选择，即临时表和表变量。这篇文章从以下几个方面来对临时表和表变量进行对比：
创建和析构方式
存储方式
作用域
对事务的支持
性能影响

创建和析构方式

临时表和表变量在创建和析构方式上是完全不一样的，在这一节，我们会从以下几点来看看他们的不同。
结构定义
索引创建
DDL
析构方式

结构定义

在上一篇文章SQL Server 临时表和变量系列之概念篇中，我们已经知道了临时表的定义方式是CREATE TABLE，而表变量的定义方式是DECLARE TABLE，在此我们不再过多的累述。让我们重温代码既可：

USE tempdb
GO
-- ************Temp table 
-- If exists named temp table, drop it.
IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO

-- And then create a new one
CREATE TABLE #tb_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
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  CONSTRAINT DF_tbTable_Indate DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

-- ************table variable
-- we don't need to check existence, declare directly.
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)
);

索引创建

临时表和表变量关于索引的创建方式是完全不一样的。临时表可以定义表结构之中或之后创建索引，而表变量只能在定义的过程中创建。如果在上面结构定义的语句之后，再执行下面的语句，第一个语句可以成功执行，而第二条语句会报错。

-- Create index for temptable, success
CREATE INDEX IX_ProductName
ON #tb_table(ProductName);
GO

-- Try to create index for table variable, failed
CREATE INDEX IX_ProductName
ON @tb_table(ProductName);
GO

执行结果如下：

DDL

关于临时表和表变量的DDL操作，与索引创建类似。临时表可以在定以后进行任何的DDL操作，而表变量在完成定以后，是完全禁止DDL操作的。假设我们需要为临时表加上一个新的字段ModifiedDate时间字段，同时我们也尝试为表变量添加这个字段定义。

-- Add a new column into temp table
ALTER TABLE #tb_table
ADD ModifiedDate  DATETIME NULL
GO

-- Try to add a new column to table variable
ALTER TABLE @tb_table
ADD ModifiedDate DATETIME NULL
GO

为临时表添加字段成功，而表变量报错，截图如下：

析构方式

临时表和表变量创建成功，使用完毕以后，系统需要回收这部分资源。在析构方式上也存在差异。针对表变量，SQL Server析构的方式比较简单，在表变量所在的批处理结束后，系统会自动回收资源，无需任何的人为干预。而对于临时表相对比较复杂一些，这里需要分为全局临时表和局部临时表。
全局临时表：以##打头的临时表称为全局临时表，此类型的临时表对所有进程可见，此类型的临时表生命周期是所有使用到全局临时表的连接完全断开后，临时表资源被系统自动回收；当然我们也可以手动析构临时表，方法是使用DROP TABLE语句。
局部临时表：以#打头的临时表称为局部临时表，这种类型的临时表仅当前进程可见，其他进程不可访问，生命周期是随着当前连接进程的关闭而消亡，临时表资源被系统自动回收；相同道理，我们同样可以使用DROP TABLE语句手动回收。

注意：
如果临时表（无论全局还是局部临时表）中存放有大量记录数（比如超过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

存储方式

我时常在一些图书和网络上的文章看到很多人都会说临时表是存储在磁盘上，而表变量是存储在内存中。这种说法太过武断，并且是错误的。这一节，我们会从两个方面来讨论临时表和表变量的存储方式，通过这一节，你就知道这个观点错在那里了。
结构定义信息
数据存储

结构定义信息

结构定义信息是指创建临时表或表变量结构的定义信息，比如：约束、索引、表结构等。到底这些定义信息存放在哪里？或者说我们如何获取临时表和表变量的结构定义信息呢？在临时表创建完毕后，我们使用下面的语句可以查看：

USE tempdb
GO
;WITH DATA
AS(
    SELECT 
        *
    FROM sys.objects
    WHERE object_id = object_id('#tb_table')
    UNION ALL
    SELECT *
    FROM sys.objects
    WHERE parent_object_id = object_id('#tb_table')
)
SELECT
    parent_object = OBJECT_NAME(parent_object_id)
    ,name
    ,type_desc
    ,create_date
FROM DATA
ORDER BY create_date DESC

从展示结果来看，临时表的定义信息是存放在Tempdb数据库下的：

这里有一个非常有趣的问题需要思考下：在创建临时表的定义语句中，我们的临时表名字明明是#tb_table，为什么这里却变成了#tb_table__000000000005呢？答案我们会在“作用域
”这一节来揭晓。
那让我们来看看如何获取表变量的定义，为了看到测试效果，我特意将当前数据修改到master数据库下，然后从Tempdb下去获取表变量的定义信息（其他数据库无法获取到）：

-- ************table variable
-- we don't need to check existence, declare directly.
USE master
GO

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)
);

SELECT TOP 7 
    current_db = db_name(), name, parent_name = (SELECT TOP 1 name FROM tempdb.sys.all_objects WHERE object_id = A.parent_object_id)
    ,type_desc, create_date, [current_date] = getdate()
FROM tempdb.sys.all_objects AS A
ORDER BY create_date DESC

从展示的结果来看，我们成功的从Tempdb下获取到了表变量的定义信息，结果如下：

数据存储

写到这里，我们已经知道了临时表和表变量的定义信息均是放在Tempdb数据库下的。那么临时表和表变量的数据又是存放在哪里的呢？这一小节，我们要探讨这个问题。
首先我们来看看临时表中的数据的存储位置。我们在SSMS中开启一个连接，执行以下语句：

USE tempdb
GO
SELECT database_name = db_name(),name,type_desc,size = size/128.
FROM sys.database_files

WAITFOR DELAY '00:00:10'

SELECT database_name = db_name(),name,type_desc,size = size/128.
FROM sys.database_files

然后我们在5秒时间内开启另外一个连接，执行下面的语句

USE tempdb
GO
-- If exists named temp table, drop it.
IF OBJECT_ID('tempdb..#tb_dataLocation','U') IS NOT NULL
    DROP TABLE #tb_dataLocation
GO
CREATE TABLE #tb_dataLocation(bigChar char(8000));

DECLARE
    @do int = 1; 
WHILE @do <= 10000
BEGIN
    INSERT INTO #tb_dataLocation VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END

SELECT 
       OBJECT_NAME = object_name(object_id),
       reserved_size = SUM(reserved_page_count)/128.,
       used_pages = SUM(used_page_count),
       pages = SUM(CASE
                  WHEN index_id < 2 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
                  ELSE lob_used_page_count + row_overflow_used_page_count
              END),
       row_count = SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END)
FROM tempdb.sys.dm_db_partition_stats WITH(NOLOCK)
WHERE object_id = object_id('#tb_dataLocation', 'U')
GROUP BY object_id

从第二个连接执行结果展示来看，我们往临时表#tb_dataLocation中插入了10000条数据，总共占用了78.19MB空间。

我们再来看看第一个连接执行完毕后的结果，tempdb数据库数据文件增长了80MB，这个数字大小和临时表空间占用大小78.19非常接近了（因为我的Tempdb的Filegrowth设置为10MB，数据文件因为临时表数据的插入增长了8次）。

因此我们可以得出结论，临时表中的数据是存放在Tempdb的磁盘上数据文件中。
接下来，我们看看表变量中的数据到底是存放在哪里的？我们还是新开一个SSMS连接，执行如下语句。这段代码是统计SQL Server缓存（即内存）中BufferPool空间变化情况。

use master
GO
DBCC DROPCLEANBUFFERS

select total_cached_pages = count(1) / 128.
from sys.dm_os_buffer_descriptors b 
where b.database_id = db_id('tempdb')
and is_modified=1

WAITFOR DELAY '00:00:10'

select total_cached_pages = count(1) / 128.
from sys.dm_os_buffer_descriptors b 
where b.database_id = db_id('tempdb')
and is_modified=1

在5秒之内新开另外一个连接，执行下面的语句，表结构和临时表一致，插入的数据记录数也一致。所以，表变量中存放的数据大小也大概在78MB左右：

-- table variables
DECLARE  @tb_dataLocation TABLE(bigChar char(8000));

DECLARE
    @do int = 1; 
WHILE @do <= 10000
BEGIN
    INSERT INTO @tb_dataLocation VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END
GO

让我们看看第一个新开的连接执行的结果情况，从结果来看SQL Server数据缓存增加了78.65减去0.55，约等于78MB左右的内存空间占用，这个大小和表变量数据大小几乎一致。

因此，我们可以得出结论，表变量数据是存放在SQL Server的缓存中，即内存中（当然也会有例外情况，当SQL Server内存空间不足时，表变量数据会写入磁盘）。

作用域

临时表和表变量的另一个不同是作用域不同。

局部临时表

局部临时表是以#打头的临时表，局部临时表仅当前进程可见，其他进程不可访问，生命周期会随着当前连接进程的关闭而消亡。那么，局部临时表是如何做到仅当前会话可见呢？在局部临时表创建的时候，SQL Server会将局部临时表名后添加一串随机字符来作为局部临时表在系统中的唯一标识符，比如：#tb_table__000000000005（这里我以两个下划线来代替多个下划线），这样可以避免其他进程在创建相同临时表名字的时候导致的命名冲突（比如：两个进程同时执行了使用临时表的存储过程），这个也是“结构定义存储”小节问题的答案。我们怎么来确定这两者是同一个临时表呢？请使用下面的方法：我们先往临时表中插入一条记录，然后分别查询这两个表名字不一样的临时表（其实是同一个表），看看数据是否一样即可。

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES(NEWID(), 0.1, 0.2, 0.3);
SELECT *
FROM #tb_table

SELECT *
FROM [#tb_table___________________________________________________________________________________________________________000000000005] WITH(NOLOCK)

结果展示如下，我们可以看到这两条记录是一模一样的，所以这两个表是指同一个对象。

全局临时表

以##打头的临时表称为全局，此类型的临时表对所有进程可见，当前进程和其他进程均可访问，生命周期是所有使用到全局临时表的连接完全关闭后，临时表消亡。关于全局临时表作用域的实例演示我们已经在文章SQL Server 表变量和临时表系列之概念篇中“临时表的使用”章节涉及到了，在此不再累述。

表变量

表变量的作用域和变量作用域是一致的，都是当前批处理，因为从根本上来讲表变量就是一种特殊的本地变量。在当前连接进程关闭以后，表变量会被SQL Server数据库系统自动回收而无需用户干预。

对事务支持

按照我们前面文章的分析，临时表数据是存放在tempdb物理文件的磁盘上，是一种特殊的表结构；表变量数据是基于内存存放的一种特殊变量结构。临时表对事务的支持和普通表对象保持一致；而表变量仅在更新表的时候有事务，也就是说表变量仅在操作这张表的DML操作时候支持事务，其他情况不支持事务。
让我们来看下面的例子，代码如下：

--***************Transaction Support

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

-- create temptable
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

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

-- open a transaction
BEGIN TRAN

-- insert one record
INSERT INTO #tb_table 
    OUTPUT INSERTED.ProductName
    INTO @tb_table(ProductName)
SELECT NEWID();

SELECT comment = 'temp table',* FROM #tb_table WITH(NOLOCK)
SELECT comment = 'table variable',* FROM @tb_table

ROLLBACK

SELECT comment = 'temp table',* FROM #tb_table WITH(NOLOCK)
SELECT comment = 'table variable',* FROM @tb_table

执行结果如下：

从这个结果截图，我们可以得出：临时表支持用户事务，表变量不支持用户事务。

性能影响

临时表和表变量均可以用作临时数据暂存媒介，具有相同的功效。但是，性能有时会有天壤之别。究其原因，我的分析是下面两个主要原因：
统计信息
动态SQL

统计信息

根据之前的经验，我们不止一次遇到过用户反馈，使用表变量的存储过程性能远远不及使用临时表的存储过程（当然其他代码保持一致的，仅将表变量替换为临时表）。按理讲，表变量存放在内存中，应该比临时表存放在磁盘读写效果高，而得出这样的结论和大多数人的认识相违背。究其原因就在于本节要讨论的主题 - 统计信息。按照一般常理，大部分人是不会对表变量创建主键、索引的，这个有可能是因为表变量只能在定义时创建主键和索引导致很多人忽略了，又或者是很多人根本没有意识到要为表变量建立索引。但是，对于临时表，大家习惯于创建主键、索引的，这就导致了表变量不存在任何的统计信息，而临时表有完整的统计信息。根据之前的文章SQL Server幕后英雄－统计信息我们清楚的知道统计信息会左右SQL Server的执行计划评估，和SQL执行效率息息相关，对SQL语句的查询性能起着至关重要的作用。
查看表变量的统计信息：

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

执行结果如下：

动态SQL

临时表可以在调用动态SQL之前定义，在动态SQL中使用；而表变量只能在动态SQL中定义，否则会报告错误异常。如下事例：

-- ************Dynamic SQL

-- temp table
IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO

-- create temptable
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO #tb_table SELECT NEWID();

DECLARE
    @sql nvarchar(max)
    ;

SET
    @sql = N'SELECT * FROM #tb_table WHERE RowId = @RowId'
    ;

EXEC sys.sp_executesql @sql,N'@RowID int', @RowID = 1

-- table variable
SET @sql = N'
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();

SELECT * FROM @tb_table WHERE RowId = @RowId
';

EXEC sys.sp_executesql @sql,N'@RowID int', @RowID = 1

SQL Server 临时表和变量系列之对比篇

摘要

创建和析构方式

结构定义

索引创建

DDL

析构方式

存储方式

结构定义信息

数据存储

作用域

局部临时表

全局临时表

表变量

对事务支持

性能影响

统计信息

动态SQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景