Why is there a worktable in merge join?
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?
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.*