I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.

The test setup

Setup script, including some realistic statistics:

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;

The repro

When I join these two tables on their clustering keys, I expect a one-to-many MERGE join, like so:

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';

This is the query plan I want:

(Never mind the warnings, they have to do with the fake statistics.)

However, if I change the order of the columns around in the join, like so:

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';

... this happens:

The Sort operator seems to order the streams according to the declared order of the join, i.e. c, a, b, d, e, f, g, h, which adds a blocking operation to my query plan.

Things I've looked at

• I've tried changing the columns to NOT NULL, same results.
• The original table was created with ANSI_PADDING OFF, but creating it with ANSI_PADDING ON does not affect this plan.
• I tried an INNER JOIN instead of LEFT JOIN, no change.
• I discovered it on a 2014 SP2 Enterprise, created a repro on a 2017 Developer (current CU).

Finally, we get to the question

• Is this intentional?
• Can I eliminate the sort without changing the query (which is vendor code, so I'd really rather not...). I can change the table and indexes.