一、表变量与临时表的前世今生
在SQL Server的世界里，处理中间数据时我们常遇到两个选择：表变量和临时表。它们就像临时工，活干完就走人，但用起来却各有各的脾气。
表变量用DECLARE @t TABLE定义，生命周期仅限于当前批处理或会话；临时表则以#或##开头，作用域可跨批处理甚至会话。举个栗子：
-- 技术栈：SQL Server 2019
-- 示例1：表变量的基本用法
DECLARE @Employee TABLE (
ID INT PRIMARY KEY,
Name NVARCHAR(50) NOT NULL
);
INSERT INTO @Employee VALUES (1, '张三'), (2, '李四');
SELECT * FROM @Employee; -- 仅在当前批处理有效
-- 示例2：局部临时表
CREATE TABLE #TempEmployee (
ID INT,
Name NVARCHAR(50)
);
INSERT INTO #TempEmployee
SELECT EmployeeID, FirstName FROM Employees WHERE Department = 'IT';
-- 临时表在当前会话的所有批处理中均可用
二、性能对比的硬核真相
1. 统计信息的玄机
临时表会生成统计信息，优化器能据此制定高效执行计划；而表变量则像个"盲人"，优化器默认认为它只有1行数据。例如：
-- 示例3：临时表的统计信息优势
CREATE TABLE #LargeTemp (Data INT);
INSERT INTO #LargeTemp SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY object_id) FROM sys.objects;
-- 优化器知道这里实际有10000行数据
DECLARE @LargeVar TABLE (Data INT);
INSERT INTO @LargeVar SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY object_id) FROM sys.objects;
-- 优化器会按1行来估算，可能导致灾难性计划
2. 事务处理的差异
表变量不参与事务回滚，这个特性在某些场景下能提升性能：
-- 示例4：事务中的行为差异
BEGIN TRANSACTION;
INSERT INTO #TempTransaction VALUES (1); -- 受事务影响
INSERT INTO @VarTransaction VALUES (1); -- 不受事务影响
ROLLBACK;
-- 此时#TempTransaction为空，@VarTransaction仍有数据
三、实战中的选择策略
1. 小数据量场景
当处理<100行数据时，表变量是更好的选择。它的轻量级特性展现无遗：
-- 示例5：小数据量下的性能对比
DECLARE @SmallVar TABLE (ID INT);
INSERT INTO @SmallVar VALUES (1),(2),(3); -- 几乎零开销
CREATE TABLE #SmallTemp (ID INT); -- 需要DDL操作开销
INSERT INTO #SmallTemp VALUES (1),(2),(3);
2. 复杂查询场景
需要索引、统计信息支持时，临时表才是王道：
-- 示例6：复杂查询优化
CREATE TABLE #OrdersWithStats (
OrderID INT PRIMARY KEY,
Amount DECIMAL(18,2),
INDEX IX_Amount NONCLUSTERED (Amount)
);
-- 可以创建额外的索引
-- 表变量只能在声明时定义主键/唯一约束
四、那些年我们踩过的坑
1. 并行执行的限制
表变量会强制查询使用串行计划，这在处理大数据量时尤为致命：
-- 示例7：并行执行差异
SELECT * FROM #BigTemp WHERE Amount > 1000; -- 可能使用并行计划
SELECT * FROM @BigVar WHERE Amount > 1000; -- 强制串行执行
2. 内存压力问题
表变量完全驻留在内存，当数据量过大时可能引发内存压力：
-- 示例8：内存使用对比
DECLARE @HugeVar TABLE (Col1 CHAR(8000)); -- 可能耗尽内存
INSERT INTO @HugeVar SELECT TOP 10000 'A' FROM sys.objects;
CREATE TABLE #HugeTemp (Col1 CHAR(8000)); -- 会使用tempdb空间
五、终极选择指南
- 数据量<100行：优先选择表变量
- 需要索引/统计信息：必须用临时表
- 事务隔离需求高：考虑临时表
- 内存敏感环境：评估临时表
- 并行查询需求：临时表唯一选
记住：没有银弹，只有最适合的解决方案。下次当你面对这个选择时，不妨先问问数据量有多大、查询有多复杂、是否需要事务支持。就像选择工具一样，用对了事半功倍，用错了可能就要加班改bug了！
评论