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

Question

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.DATAAREAID = N'LAN'
       and JOURNAL.PARTITION = 5637144576
       and JOB.WRKCTRID = N'TF1'

Query plan: https://www.brentozar.com/pastetheplan/?id=rJh1GPqBn

But it should be sorted according to the index, no?

Answer 1

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