Skip to main content
added 53 characters in body
Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

I can repro this too (repro this tooFiddle).

If you change

  AND T2.INVENTSERIALID = T1.INVENTSERIALID
  AND T2.ITEMID = T1.ITEMID

To

  AND T2.ITEMID = T1.ITEMID  
  AND T2.INVENTSERIALID = T1.INVENTSERIALID

It is able to produce the desired plan (no sort operators in the final query in the Fiddle).

Somewhat unsatisfying that you have to do this but previously with MERGE join and MERGE union I have also found that tweaks are needed to get the hoped for plan (example).

Paul White previously commented on the above example

Finding optimal ordering is NP-hard so database engines rely on heuristics.

I can repro this too.

If you change

  AND T2.INVENTSERIALID = T1.INVENTSERIALID
  AND T2.ITEMID = T1.ITEMID

To

  AND T2.ITEMID = T1.ITEMID  
  AND T2.INVENTSERIALID = T1.INVENTSERIALID

It is able to produce the desired plan.

Somewhat unsatisfying that you have to do this but previously with MERGE join and MERGE union I have also found that tweaks are needed to get the hoped for plan (example).

I can repro this too (Fiddle).

If you change

  AND T2.INVENTSERIALID = T1.INVENTSERIALID
  AND T2.ITEMID = T1.ITEMID

To

  AND T2.ITEMID = T1.ITEMID  
  AND T2.INVENTSERIALID = T1.INVENTSERIALID

It is able to produce the desired plan (no sort operators in the final query in the Fiddle).

Somewhat unsatisfying that you have to do this but previously with MERGE join and MERGE union I have also found that tweaks are needed to get the hoped for plan (example).

Paul White previously commented on the above example

Finding optimal ordering is NP-hard so database engines rely on heuristics.

Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

I can repro this too.

If you change

  AND T2.INVENTSERIALID = T1.INVENTSERIALID
  AND T2.ITEMID = T1.ITEMID

To

  AND T2.ITEMID = T1.ITEMID  
  AND T2.INVENTSERIALID = T1.INVENTSERIALID

It is able to produce the desired plan.

Somewhat unsatisfying that you have to do this but previously with MERGE join and MERGE union I have also found that tweaks are needed to get the hoped for plan (example).