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:

[![This is what I want.][1]][1]

(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 query plan after changing the declared column order in the join.][2]][2]

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.



  [1]: https://i.sstatic.net/HnXXA.png
  [2]: https://i.sstatic.net/qw7bT.png