为什么更改声明的连接列顺序会引入排序？

我有两个具有相同名称、类型和索引键列的表。其中一个具有唯一的聚集索引，另一个具有非唯一的.

测试设置

设置脚本，包括一些真实的统计数据：

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
    a       char(4) NOT NULL,
    b       char(2) NOT NULL,
    c       varchar(13) NOT NULL,
    d       bit NOT NULL,
    e       char(4) NOT NULL,
    f       char(25) NULL,
    g       char(25) NOT NULL,
    h       char(25) NULL
    --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

复制品

当我在它们的集群键上加入这两个表时，我期望一个一对多的 MERGE 连接，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.a=r.a AND
    l.b=r.b AND
    l.c=r.c AND
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

这是我想要的查询计划：

（不要介意警告，它们与虚假统计有关。）

但是，如果我更改连接中列的顺序，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
    l.c=r.c AND     -- used to be third
    l.a=r.a AND     -- used to be first
    l.b=r.b AND     -- used to be second
    l.d=r.d AND
    l.e=r.e AND
    l.f=r.f AND
    l.g=r.g AND
    l.h=r.h
WHERE l.a='2018';

... 有时候是这样的：

Sort 运算符似乎根据声明的连接顺序对流进行排序，即c, a, b, d, e, f, g, h，它向我的查询计划添加了阻塞操作。

我看过的东西

• 我尝试将列更改为NOT NULL，结果相同。
• 原始表是用 创建的ANSI_PADDING OFF，但创建它ANSI_PADDING ON不会影响这个计划。
• 我尝试了一个INNER JOIN而不是LEFT JOIN，没有变化。
• 我在 2014 SP2 Enterprise 上发现了它，在 2017 Developer（当前 CU）上创建了一个复制品。
• 删除前导索引列上的 WHERE 子句确实会产生好的计划，但它会影响结果.. :)

最后，我们进入问题

• 这是故意的吗？
• 我可以在不更改查询的情况下消除排序吗（这是供应商代码，所以我真的不想......）。我可以更改表和索引。