Sort or No sort before merge join depending on order columns in join condition

Wilfried 2017-10-23 19:36:29

Both table PODRIA_Complete and PODRIA_Intermediate have a primary key on columns MC_Market_Code, Account, Product, Audit_Date, Activity. Column in the order as mentioned. MC_Market_Code is in both large tables (~ 500 million rows) the partition key.
When joining both tables on the PK columns, the order of columns mentioned in join condition do determine if sort is needed (unwanted) or not.
Is this expected behaviour of SQL Server?