如何在SQL Server中避免重复表扫描，跨多列合并结果集值？

问题背景 #

现有数据库表结构如下：

CREATE TABLE Foo (id INT, x1 INT, x2 INT, x3 INT, ...)
CREATE TABLE Bar (id INT, fooId INT, x4 INT, ...)
CREATE TABLE Qux (x INT, ...)

Foo和Bar表均以id为唯一主键，Bar通过fooId外键关联单个Foo记录，该schema符合业务规范化要求。

需求为：获取符合特定WHERE条件的Foo记录及其关联Bar表中，来自x1、x2、x3、x4列的唯一x值集合，再关联查询Qux表的对应记录。

原实现采用多次UNION ALL拼接列数据，但SQL Server会重复扫描Foo和Bar表，导致生产系统性能显著下降：

WITH CTE_Ids AS (
    SELECT x1 AS x FROM Foo WHERE ...
    UNION ALL SELECT x2 AS x FROM Foo WHERE ...
    UNION ALL SELECT x3 AS x FROM Foo WHERE ...
    UNION ALL SELECT x4 AS x FROM Foo f LEFT OUTER JOIN Bar b ON f.id = b.fooId WHERE ...
),
CTE_UniqueIds AS (
    SELECT DISTINCT x FROM CTE_Ids
)
SELECT q.*
FROM CTE_UniqueIds ids
INNER JOIN Qux q ON ids.x = q.x

优化方案 #

核心思路是仅扫描一次Foo和关联的Bar表，将多列数据转换为行结构后再去重，最后关联Qux表。

方法1：使用VALUES行构造器（SQL Server 2008+支持） #

先一次性拉取符合条件的Foo及其关联Bar的所有目标列，通过CROSS APPLY + VALUES完成列转行，最后去重关联：

WITH CTE_FooBar AS (
    -- 仅执行一次Foo与Bar的连接查询，扫描一次表
    SELECT 
        f.x1, f.x2, f.x3, b.x4
    FROM Foo f
    LEFT JOIN Bar b ON f.id = b.fooId
    WHERE f... -- 此处仅需编写一次Foo的WHERE过滤条件
),
CTE_Unpivoted AS (
    SELECT x
    FROM CTE_FooBar
    -- 将多列拆转为单行记录
    CROSS APPLY (
        VALUES 
            (x1), 
            (x2), 
            (x3), 
            (x4)
    ) AS Unpivoted(x)
    WHERE x IS NOT NULL -- 按需过滤空值，减少后续处理数据量
),
CTE_UniqueIds AS (
    SELECT DISTINCT x FROM CTE_Unpivoted
)
SELECT q.*
FROM CTE_UniqueIds ids
INNER JOIN Qux q ON ids.x = q.x

方法2：使用UNPIVOT运算符（SQL Server 2005+支持） #

逻辑与方法1一致，通过UNPIVOT运算符完成列转行：

WITH CTE_FooBar AS (
    SELECT 
        f.x1, f.x2, f.x3, b.x4
    FROM Foo f
    LEFT JOIN Bar b ON f.id = b.fooId
    WHERE f... -- 仅一次WHERE过滤
),
CTE_Unpivoted AS (
    SELECT x
    FROM CTE_FooBar
    UNPIVOT (
        x FOR ColumnName IN (x1, x2, x3, x4)
    ) AS UnpivotTable
    WHERE x IS NOT NULL
),
CTE_UniqueIds AS (
    SELECT DISTINCT x FROM CTE_Unpivoted
)
SELECT q.*
FROM CTE_UniqueIds ids
INNER JOIN Qux q ON ids.x = q.x

关键优化点 #

• 仅执行一次Foo和Bar的连接查询，彻底避免重复表扫描
• 列转行操作基于已加载到内存的数据集完成，性能开销远低于重复扫描磁盘表
• 可通过x IS NOT NULL过滤无效值，进一步减少去重和关联阶段的数据量

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