问题引入

“菜鸟啊，最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题，你去深入探讨下这个问题吧，解答解答他们的疑惑吧”，老鸟又开始为菜鸟找活干了。
“鸟哥啊，关于表变量和临时表使用选择的问题啊，向来行业里争论不休，我比较担心我们的观点被人家拍砖啊”。
“鸟啊，有争论才说明这个问题有价值啊，所以我们才更应该去弄清楚，道明白啊”。反正老鸟总会找到合适的理由。
“那好吧，要把这个问题要刨根问底，我们需要分四篇文章来把这个问题理清楚。”，菜鸟掰着手指头就数了出来：
 表变量和临时表基本概念
 表变量和临时表的对比
 表变量和临时表认知误区
 表变量和临时表的选择

什么是表变量

关于什么是SQL Server的表变量，我们分别从表变量的定义、表变量的作用和表变量的使用三个角度来看看什么是表变量。

表变量定义

表变量，是微软至SQL Server 2000以来引入的概念，从名称我们就可以很容易看出，表变量本质是一个变量，只是它具有了正式表对象的很多属性。比如：它有表字段、字段数据类型、字段宽度、主键、唯一约束、NULL、NOT NULL约束、CHECK和DEFAULT约束。但是，表变量不支持约束命名，不支持索引，不支持外键，不支持表变量定义后的任何表变量结构的修改，仅可做数据的DML操作。

表变量的作用

当我们需要在当前会话临时缓存少量的中间数据结果集，供当前会话多次使用这同一数据集或者同一数据结果集的一部分时，我们可以考虑使用表变量，表变量中的数据是缓存在内存中（大部分情况下如此，也有极少情况例外，我们后面的文章会讲到）。注意这里是少量数据集，不是大量结果集，如果非要给一个参照经验值的话，个人建议是最好不要超过10万条数据记录，所占的空间大小不要超过100MB。

表变量的使用

关于表变量作用，在此我们以一个例子来说明。在这个例子中，我们定义了一个表变量来暂时存放商品的基本属性信息，然后INSERT了三条数据，紧接着对其中一条数据做UPDATE操作，再接着DELETE了一条数据，最后我们SELECT了整个表变量存放的数据。

USE tempdb
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())
);

INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('A', 0.1, 0.2, 0.3);
INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('B', 0.4, 0.5, 0.6);
INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('C', 0.7, 0.8, 0.9);

UPDATE A
SET Length = 2.5
FROM @tb_table AS A
WHERE RowID = 1
;

DELETE TOP(1) A
FROM @tb_table AS A
WHERE RowID = 2;

SELECT * FROM @tb_table;

从这个例子，我们看到了表变量所具有的正式表对象的属性，表变量是如何定义的，以及DML操作，在当前会话结束后，表变量会被SQL Server自动回收。
这里需要特别提醒下，SQL Server系统不允许我们像正式表对象那样对约束进行显示命名，SQL Server会报告错误。比如，定义表变量代码：

USE tempdb
GO
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(8,2) NOT NULL
,Windth DECIMAL(8,2) NOT NULL
,Height DECIMAL(8,2) NOT NULL
,Indate DATETIME NOT NULL  CONSTRAINT DF_tbTable DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

报错信息如下：

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CONSTRAINT'.

什么是临时表

在看完什么是表变量以后，我们还是分别从临时表定义、临时表的作用和临时表的使用三个角度来看看什么是SQL Server的临时表。

临时表定义

SQL Server的临时表是一种特殊的表，表名字是以#或者##打头。无论临时表在哪个数据库下创建，SQL Server均把临时表结构信息和数据存储在Tempdb数据库下。
以#打头的临时表称为局部临时表，这种类型的临时表仅当前进程可见，其他进程不可访问，生命周期会随着当前连接进程的关闭而消亡。
以##打头的临时表称为全局，此类型的临时表对所有进程可见，当前进程和其他进程均可访问，生命周期是所有使用到全局临时表的连接完全关闭后，临时表消亡。

临时表的作用

临时表的作用和表变量类似，均是用于暂时缓存数据。临时表中的数据会被储存在Tempdb的物理文件磁盘上，当需要数据读取时，SQL Server会将临时表中数据从磁盘文件读入SQL Server Buffer Pool中，然后返回给客户端。因此，临时表对数据的存储和读取会有物理的IO Write和IO Read的。临时表相较于表变量可以存储稍微大量一些的数据，比如数据量超过10万条记录数，数据空间占用量超过100MB。但是，如果经常有类似的临时表使用场景时，建议对Tempdb数据库做性能优化相关的配置工作。

临时表的使用

为了和表变量形成对比，我特意将表结构和数据保持一致，不同的地方在于，我们可以对约束进行显示指定命名，可以创建索引。在次，为了看清楚局部临时表和全局临时表的区别，我们也创建了一个全局临时表。

USE tempdb
GO
IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
    DROP TABLE #tb_table
GO
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 DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

CREATE INDEX IX_ProductName
ON #tb_table(ProductName);
GO

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('A', 0.1, 0.2, 0.3);
INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('B', 0.4, 0.5, 0.6);
INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('C', 0.7, 0.8, 0.9);

IF OBJECT_ID('tempdb..##tb_table','U') IS NOT NULL
    DROP TABLE ##tb_table
GO
SELECT * 
    INTO ##tb_table
FROM #tb_table;

UPDATE A
SET Length = 2.5
FROM #tb_table AS A
WHERE RowID = 1
;

DELETE TOP(1) A
FROM #tb_table AS A
WHERE RowID = 2;

SELECT * FROM #tb_table;

SELECT *
FROM ##tb_table

执行上面的局部临时表和全局临时表创建语句之后，我们在SSMS中新开启一个连接，执行下面的语句：

SELECT *
FROM ##tb_table
GO

SELECT *
FROM #tb_table

返回执行结果如下：

返回执行消息如下：

从返回的结果分析可知：局部临时表仅当前连接可以访问，对其他进程不可见（访问报告对象不存在的错误），而全局临时表不仅当前连接可以访问，对其他进程可见。

写在最后

关于SQL Server表变量和临时表的使用规则是一个仁者见仁智者见智的话题，所以我们希望能够把这个话题尽可能的剖析清楚，让读者对两者有非常清楚的认识。