Skip to main content
1 of 2
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).

Martin Smith
  • 86.5k
  • 15
  • 252
  • 342