SQL Server：WITH表达式与临时表对TempDB空间占用对比问询

示例A与B的TempDB占用对比 #

核心差异分析 #

1. 数据量差异

   • 示例A使用UNION，会自动剔除重复的id和name记录，最终处理的是去重后的结果集，行数更少。
   • 示例B通过三次INSERT将三个查询的所有结果（含重复记录）存入临时表#data，临时表的行数等于三个分支结果的总和，数据量更大。

2. TempDB开销差异

   • 示例A：UNION操作需要对三个分支结果排序去重，这一步会占用TempDB的工作空间；后续ORDER BY + OFFSET/FETCH的排序开销基于去重后的小结果集。
   • 示例B：临时表本身会占用更多TempDB空间存储全量数据；后续排序分页时，因数据量更大，排序操作的TempDB开销也更高。

结论：示例A的TempDB空间占用通常远小于示例B

更优解决方案 #

1. 替换UNION为UNION ALL（业务允许时） #

如果业务逻辑中Table4的id不会重复（比如systemType+systemId组合唯一，每个id仅出现在一个分支），用UNION ALL替代UNION，可以省去去重排序的TempDB开销，大幅降低空间占用和执行时间：

WITH data1 AS (
SELECT
t4.[id] AS [id],
t1.name1 AS [name]
FROM TABLE1 t1
INNER JOIN TABLE4 t4 ON t4.[systemId] = t1.[id] and t4.[systemType] = 'Table1'
UNION ALL
SELECT
t4.[id] AS [id],
t2.name1 AS [name]
FROM TABLE2 t2
INNER JOIN TABLE4 t4 ON t4.[systemId] = t2.[id] and t4.[systemType] = 'Table2'
UNION ALL
SELECT
t4.[id] AS [id],
t3.name1 AS [name]
FROM TABLE3 t3
INNER JOIN TABLE4 t4 ON t4.[systemId] = t3.[id] and t4.[systemType] = 'Table3'
)
SELECT id, name
FROM data1
ORDER BY [id]
OFFSET (@PageSize * @PageIndex) ROWS FETCH NEXT @PageSize ROWS ONLY;

2. 提前分页，缩小中间结果集 #

不要先合并全量数据再分页，而是先获取分页范围内的id，再关联查询其他字段，避免处理大量无关数据：

WITH paginated_ids AS (
    SELECT id
    FROM (
        SELECT t4.id FROM TABLE1 t1 JOIN TABLE4 t4 ON t4.systemId = t1.id AND t4.systemType = 'Table1'
        UNION ALL
        SELECT t4.id FROM TABLE2 t2 JOIN TABLE4 t4 ON t4.systemId = t2.id AND t4.systemType = 'Table2'
        UNION ALL
        SELECT t4.id FROM TABLE3 t3 JOIN TABLE4 t4 ON t4.systemId = t3.id AND t4.systemType = 'Table3'
    ) AS all_ids
    ORDER BY id
    OFFSET (@PageSize * @PageIndex) ROWS FETCH NEXT @PageSize ROWS ONLY
)
SELECT 
    p.id,
    CASE 
        WHEN EXISTS(SELECT 1 FROM TABLE1 t1 JOIN TABLE4 t4 ON t4.systemId = t1.id AND t4.systemType = 'Table1' AND t4.id = p.id) THEN (SELECT t1.name1 FROM TABLE1 t1 JOIN TABLE4 t4 ON t4.systemId = t1.id AND t4.systemType = 'Table1' AND t4.id = p.id)
        WHEN EXISTS(SELECT 1 FROM TABLE2 t2 JOIN TABLE4 t4 ON t4.systemId = t2.id AND t4.systemType = 'Table2' AND t4.id = p.id) THEN (SELECT t2.name1 FROM TABLE2 t2 JOIN TABLE4 t4 ON t4.systemId = t2.id AND t4.systemType = 'Table2' AND t4.id = p.id)
        ELSE (SELECT t3.name1 FROM TABLE3 t3 JOIN TABLE4 t4 ON t4.systemId = t3.id AND t4.systemType = 'Table3' AND t4.id = p.id)
    END AS name
FROM paginated_ids p;

3. 优化临时表（若必须使用） #

如果坚持用临时表方案，给临时表添加索引避免排序开销：

CREATE TABLE #data
(
     [id]                  int PRIMARY KEY CLUSTERED,
     [name]                NVARCHAR(64),
);
-- 后续INSERT语句不变

主键索引会让临时表的数据按id有序存储，后续ORDER BY id无需额外排序，减少TempDB的排序开销。

4. 优化基础表索引 #

给Table4创建复合索引(systemType, systemId)，让三个JOIN查询可以快速定位数据，减少全表扫描，降低中间结果集的大小：

CREATE NONCLUSTERED INDEX IX_Table4_systemType_systemId ON Table4(systemType, systemId) INCLUDE(id);

内容的提问来源于stack exchange，提问作者Abouch