Gullimeel (7/11/2012)
It is not related to index. This is the case when your best index is used to get the data specially from Inner table for merge join.
I Now that I have link to your entire recreation code maybe I can test your scenario and see what you are talking about.
Although at first glance it looks like something I, as a DBA, would run a few times and then check Missing Indexes DMV. Something that I would do for any query Joining data that showed a performance issue when seeking the scan key.
I see where what you are pointing out happens. Just not how this is something unique to how MERGE JOIN is calculated by optimizer. 😀