SQL server chooses hash match over merge join but the input fields to join should be sorted

I have a simple query that joins two tables on the field PRODID. For some reason, SQL Server opts to use a hash match to join this, but I believe it should choose a merge join, as that field is part of the index, and the preceding fields of both indexes are already used to filter out most of the data. So the next field in both indexes is PRODID, which should be sorted.

The query:

select JOURNAL.PRODID, JOURNAL.JOURNALID
      from PRODJOURNALTABLE JOURNAL
           inner join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
     where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
       and JOURNAL.POSTED = 1
       and JOURNAL.JOURNALTYPE = 1
       and JOURNAL.DATAAREAID = N'LAN'
       and JOURNAL.PARTITION = 5637144576
       and JOB.WRKCTRID = N'TF1'

The query plan
https://www.brentozar.com/pastetheplan/?id=B1oyc8qBh

The used indexes

CREATE NONCLUSTERED INDEX [I_243ROLLERRORVIEWIDX] ON [dbo].[PRODJOURNALTABLE]
    (
        [PARTITION] ASC,
        [DATAAREAID] ASC,
        [JOURNALTYPE] ASC,
        [POSTED] ASC,
        [POSTEDDATETIME] ASC,
        [PRODID] ASC
    )
    INCLUDE([JOURNALID])

    CREATE NONCLUSTERED INDEX [I_258ROLLERRORVIEWIDX] ON [dbo].[PRODROUTEJOB]
    (
        [PARTITION] ASC,
        [DATAAREAID] ASC,
        [WRKCTRID] ASC,
        [PRODID] ASC,
        [OPRNUM] ASC
    )
    INCLUDE([OPRPRIORITY])

EDIT:

Adding the JOIN HINT merge join to the query reveals that it tries to sort the data from the PRODJOURNALTABLE on the field PRODID

```
select JOURNAL.PRODID, JOURNAL.JOURNALID
from PRODJOURNALTABLE JOURNAL
inner merge join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
and JOURNAL.POSTED = 1
and JOURNAL.JOURNALTYPE = 1
and JOURNAL.DAT

sorta kinda

Index order is maintained for equality predicates, but you have an inequality (range) predicate:

JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'

Which results in the sort operator needing to put PRODID in correct order for the merge join:

In order to get a merge join plan without a sort, you would need to change your index to this:

CREATE NONCLUSTERED INDEX [I_243ROLLERRORVIEWIDX] 
ON [dbo].[PRODJOURNALTABLE]
(
    [PARTITION] ASC,
    [DATAAREAID] ASC,
    [JOURNALTYPE] ASC,
    [POSTED] ASC,
    [PRODID] ASC,
    [POSTEDDATETIME] ASC
)
INCLUDE([JOURNALID]);

You would no longer get a seek predicate for POSTEDDATETIME, but that may not be of much consequence since you have a number of other predicates at play.

I talk about this here:

• A Little About How Indexes Store Data In SQL Server

StackExchange Database Administrators Q#327432, answer score: 4