问题引入

“菜鸟啊，最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题，你去深入探讨下这个问题吧，解答解答他们的疑惑吧”，老鸟又开始为菜鸟找活干了。

“鸟哥啊，关于表变量和临时表使用选择的问题啊，向来行业里争论不休，我比较担心我们的观点被人家拍砖啊”。“鸟啊，有争论才说明这个问题有价值啊，所以我们才更应该去弄清楚，道明白啊”。反正老鸟总会找到合适的理由。“那好吧，要把这个问题要刨根问底，我们需要分四篇文章来把这个问题理清楚。”，菜鸟掰着手指头就数了出来： 表变量和临时表基本概念 表变量和临时表的对比 表变量和临时表认知误区 表变量和临时表的选择

什么是表变量

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

表变量定义

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

表变量的作用

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

表变量的使用

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

USE tempdbGODECLARE    @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 ASET Length = 2.5FROM @tb_table AS AWHERE RowID = 1;DELETE TOP(1) AFROM @tb_table AS AWHERE RowID = 2;SELECT * FROM @tb_table;

从这个例子，我们看到了表变量所具有的正式表对象的属性，表变量是如何定义的，以及DML操作，在当前会话结束后，表变量会被SQL Server自动回收。

这里需要特别提醒下，SQL Server系统不允许我们像正式表对象那样对约束进行显示命名，SQL Server会报告错误。比如，定义表变量代码：

USE tempdbGODECLARE    @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 10Incorrect 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 tempdbGOIF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL    DROP TABLE #tb_tableGOCREATE 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_ProductNameON #tb_table(ProductName);GOINSERT 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_tableGOSELECT *     INTO ##tb_tableFROM #tb_table;UPDATE ASET Length = 2.5FROM #tb_table AS AWHERE RowID = 1;DELETE TOP(1) AFROM #tb_table AS AWHERE RowID = 2;SELECT * FROM #tb_table;SELECT *FROM ##tb_table

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

SELECT *FROM ##tb_tableGOSELECT *FROM #tb_table

返回执行结果如下：

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

写在最后

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