恒定扫描假脱机

为什么优化器会在这里引入 spool，这样做的原因是什么？除了线轴之外，没有什么复杂的。

spool 之外的东西不是一个简单的表引用，它可以在生成左连接/反半连接替代方案时简单地复制。

它可能看起来有点像一个表（恒定扫描），但对于优化器*，它是子句UNION ALL中的一个单独的行。VALUES

额外的复杂性足以让优化器选择假脱机并重放源行，而不是稍后用简单的“表获取”替换假脱机。例如，完全连接的初始转换如下所示：

请注意一般变换引入的额外线轴。简单表 get 上方的线轴稍后按规则清理SpoolGetToGet。

如果优化器有相应的SpoolConstGetToConstGet规则，原则上它可以按你的意愿工作。

在这种情况下如何摆脱它，有哪些可能的方法？

使用真实表（临时表或变量表），或手动编写从全连接转换而来的转换，例如：

WITH 
    p([Id], [Code]) AS
    (
        SELECT @id1, 'A'
        UNION ALL
        SELECT @id2, 'B'
    ),
    FullJoin AS
    (
        SELECT
            p.Code,
            d.[Status]
        FROM p
        LEFT JOIN #data d 
            ON d.[Id] = p.[Id]
        UNION ALL
        SELECT
            NULL,
            D.[Status]
        FROM #data AS D
        WHERE NOT EXISTS
        (
            SELECT *
            FROM p
            WHERE p.Id = D.Id
        )
    )
SELECT
    COALESCE(FullJoin.Code, 'X') AS Code,
    COALESCE(FullJoin.Status, 0) AS [Status]
FROM FullJoin;

手动重写计划：

这估计成本为 0.0067201 单位，而原始成本为 0.0203412 单位。

* 它可以LogOp_UnionAll在转换树(TF 8605) 中观察到。在输入树(TF 8606) 中，它是一个LogOp_ConstTableGet. 转换树显示了在解析、规范化、代数化、绑定和一些其他准备工作之后的优化器表达式元素树。输入树显示了转换为否定范式（NNF 转换）、运行时常量折叠以及其他一些小部件后的元素。NNF 转换包括用于折叠逻辑联合和公共表获取的逻辑等。

VALUES表假脱机只是从子句中存在的两组元组中创建一个表。

您可以通过首先将这些值插入临时表来消除假脱机，如下所示：

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

DROP TABLE IF EXISTS #p;
CREATE TABLE #p
(
    Id int NOT NULL
    , Code char(1) NOT NULL
);

DECLARE @id1 int = 101, @id2 int = 105;

INSERT INTO #p (Id, Code)
VALUES
        (@id1, 'A'),
        (@id2, 'B');


SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM #p p
    FULL JOIN #data d ON d.[Id] = p.[Id];

查看查询的执行计划，我们看到输出列表包含使用Union前缀的两列；这暗示 spool 正在从联合源创建一个表：

这FULL OUTER JOIN需要 SQL Server 访问p两次值，一次用于连接的每个“边”。创建假脱机允许生成的内部循环连接以访问假脱机数据。

有趣的是，如果将 替换为FULL OUTER JOINaLEFT JOIN和 a RIGHT JOIN，并将UNION结果放在一起，SQL Server 不使用假脱机。

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    LEFT JOIN #data d ON d.[Id] = p.[Id]
UNION
SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (101, 'A'),
        (105, 'B')
    ) p([Id], [Code])
    RIGHT JOIN #data d ON d.[Id] = p.[Id];

注意，我不建议使用UNION上面的查询；对于较大的输入集，它可能不会比FULL OUTER JOIN您已经拥有的简单输入更有效。