“这句 SQL 用临时表还是表变量？”
这是 SQL Server 开发中被问得最多的问题之一。网上说法五花八门：
有人说“表变量快，放内存”，有人说“临时表才靠谱”，还有人说“数据量小就用表变量”。
其实，两者没有绝对的好坏，只有适合不适合。
这篇文章不讲玄学，只讲原理 + 实战场景，帮你做对选择。
一、先搞清楚：它们到底是什么？
1. 临时表（#TempTable）
CREATE TABLE #TempUser
(
UserID INT PRIMARY KEY,
UserName NVARCHAR(50)
);
特点：
- 存储在 tempdb
- 有完整的表结构：可以建索引、统计信息
- 会话级（#）或全局级（##）
- 支持事务回滚
- 会生成执行计划中的物理操作符（Table Scan / Seek）
一句话：临时表 = 一张“真表”，只是放在 tempdb 里，用完就丢。
2. 表变量（@TableVariable）
DECLARE @User TABLE
(
UserID INT PRIMARY KEY,
UserName NVARCHAR(50)
);
特点：
- 也存储在 tempdb
- 支持主键、唯一约束
- 不支持显式索引（除主键/唯一约束外）
- 没有统计信息
- 作用域仅限于当前批处理
- 不受事务回滚影响（这点常被忽略）
一句话：表变量 = 有表结构的变量，更像“加强版数组”。
二、核心差异对比（重点）
| 对比项 | 临时表 #Temp | 表变量 @Table |
|---|---|---|
| 存储位置 | tempdb | tempdb |
| 统计信息 | ✅ 有 | ❌ 无 |
| 显式索引 | ✅ 支持 | ❌（仅主键/唯一） |
| 执行计划 | 重编译、成本估算较准 | 固定预估（通常 1 行） |
| 事务影响 | 参与回滚 | ❌ 不回滚 |
| 作用域 | 会话 / 全局 | 当前批处理 |
| 并行查询 | ✅ 支持 | ❌ 不支持 |
| 锁/日志 | 正常表行为 | 较少，但非“纯内存” |
重要纠正一个常见误解：
表变量不是一定在内存中，数据量大时一样会落盘到 tempdb。
三、为什么“预估行数”是选型的命门？
这是理解两者差异的关键。
表变量的致命弱点：永远“看起来只有 1 行”
SQL Server 对表变量的行数预估，默认是 1 行。
DECLARE @t TABLE (id INT); -- 实际插入 10 万行
执行计划中，优化器仍然认为 @t 只有 1 行，于是可能选择：
- Nested Loops Join
- 不合适的内存分配
- 错误的索引策略
当实际数据量很大时，性能会急剧恶化。
临时表：有统计信息，预估更准
临时表会像普通表一样维护统计信息，优化器能知道：
- 大概有多少行
- 数据分布如何
因此能选择更合理的执行计划。
结论：数据量一大，表变量的执行计划风险远高于临时表。
四、实战场景对比（重点看这里）
场景 1：小数据量 + 简单使用 ✅ 表变量
适合表变量：
- 数据量小（经验值：几百行以内）
- 只做一次简单查询，无复杂 JOIN
- 不需要索引
- 不希望被事务回滚影响
示例：
DECLARE @Dept TABLE (DeptID INT PRIMARY KEY); INSERT INTO @Dept SELECT DeptID FROM Departments WHERE IsActive = 1; SELECT * FROM Users u JOIN @Dept d ON u.DeptID = d.DeptID;
优点：代码简洁、无统计信息维护开销、清理自动完成。
场景 2：大数据量 + 多次 JOIN ✅ 临时表
适合临时表：
- 数据量几千 / 几万 / 更多
- 需要多次 JOIN、WHERE、ORDER BY
- 需要非聚集索引
- 希望执行计划准确
示例：
CREATE TABLE #OrderTemp
(
OrderID INT PRIMARY KEY,
UserID INT,
Amount DECIMAL(18,2)
);
CREATE INDEX IX_UserID ON #OrderTemp(UserID);
INSERT INTO #OrderTemp
SELECT OrderID, UserID, Amount
FROM Orders
WHERE OrderDate >= '2025-01-01';
SELECT u.UserName, SUM(o.Amount)
FROM Users u
JOIN #OrderTemp o ON u.UserID = o.UserID
GROUP BY u.UserName;
优点：执行计划合理、可建索引、性能稳定。
场景 3：需要事务回滚 ✅ 临时表
BEGIN TRAN; INSERT INTO #TempLog VALUES (1, 'start'); ROLLBACK; -- #TempLog 中的数据会回滚消失
表变量在 ROLLBACK 后 不会回滚，这在日志、中间状态处理中可能是灾难。
涉及事务一致性，优先临时表。
场景 4：动态 SQL + 跨作用域 ✅ 临时表
表变量不能跨批处理传递：
DECLARE @t TABLE (id INT); EXEC sp_executesql N'SELECT * FROM @t'; -- ❌ 报错
临时表可以：
CREATE TABLE #t (id INT); EXEC sp_executesql N'SELECT * FROM #t'; -- ✅
动态 SQL、存储过程嵌套调用，用临时表。
场景 5：并行查询需求 ✅ 临时表
表变量 不支持并行查询，临时表支持。
在大数据量聚合、复杂查询中，并行度对性能影响巨大。
CPU 密集型、大表处理，用临时表。
五、一个典型“踩坑”案例
问题 SQL：
DECLARE @IDs TABLE (ID INT PRIMARY KEY); INSERT INTO @IDs SELECT ID FROM BigTable WHERE Status = 1; -- 10 万行 SELECT * FROM BigTable b JOIN @IDs i ON b.ID = i.ID WHERE b.CreateTime > '2025-01-01';
现象：
- 查询极慢
- 执行计划中 Nested Loops 成本极高
原因：
-
优化器认为
@IDs只有 1 行 - 实际 10 万行
- 导致大表被循环扫描
解决：
CREATE TABLE #IDs (ID INT PRIMARY KEY); -- 其余逻辑不变
性能立刻提升几十倍。
六、决策流程图（实战速查）
数据量小（< 几百行）？
├─ 是 → 表变量 ✅
└─ 否 → 需要索引 / 多次 JOIN / 并行 / 事务回滚？
├─ 是 → 临时表 ✅
└─ 否 → 表变量（可尝试）
七、进阶技巧：临时表的“正确打开方式”
1. 显式建索引，不要依赖主键
CREATE TABLE #Temp
(
ID INT,
CreateTime DATETIME
);
CREATE CLUSTERED INDEX IX_CreateTime ON #Temp(CreateTime);
2. 用完及时清理（尤其在循环中）
DROP TABLE IF EXISTS #Temp;
3. 大数据插入，先插再建索引
INSERT INTO #Temp SELECT ... FROM BigTable; CREATE INDEX IX_X ON #Temp(Col);
八、一句话总结
小数据、简单用 → 表变量；
大数据、复杂查、要准确执行计划 → 临时表。**
不要迷信“表变量更快”，也不要一上来就建临时表。
看数据量、看使用方式、看执行计划，才是正解。
