Why is there a worktable in merge join?

GokhanVarol 2013-06-23 03:19:50

This query is related to the last step in both of the execution plans. When tables switch in the merge join the duration changed twice and I noticed there is work table. The column Property_CNTY_LGL_TXT in #PropertyTable_Diablo is varchar(max) (meaning it's lob maybe it's related to that).
In one query I was trying to insert into an existing table with clustered index vs doing an into and then creating an index, as I was testing that I realized that changed the behavior of the merge join right after.
Why would one of this merge join take longer and have a worktable and the other would not?
Thank you

alt text
link text

GokhanVarol 2013-06-23 03:29:32
I added option force order and worktable is gone. I do not understand why the optimizer picked a different order when the table was manually created versus created with into clause in the original question. I still do not understand the need to the worktable.

link text

SQLkiwi 2013-06-24 10:36:41
A worktable is required when the merge join is many-to-many.

From Understanding Merge Joins in Books Online:

The merge join operation may be either a regular or a many-to-many operation. A many-to-many merge join uses a temporary table to store rows. If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.*

GokhanVarol 2013-06-24 12:29:05
Initiating the many to many merge join from the table that has more coverage on the join keys could be advantageous it seems like it (that explains this sample)?
First creating the (#tempdb) table with clustered index vs inserting into a table and then creating the clustered index, that generated a different merge join order, that could be related to statistics maybe or?