When a worktable is created for a merge join that requires a sort, only the columns that are needed for the result set and for later joins in the query execution are selected into the worktable. When the worktable for the titles table is created for the join shown in Figure 23-5:
Worktable1 includes the price and authors.state, because they are part of the result set, and pub_id, because it is needed for a subsequent join.
Worktable2 includes the publishers.state column because it is part of the result set, and the pub_id, because it is needed for the merge step.
The type column is used as a search argument while the rows from titles are selected, but since it is not used later in the query or in the result set, it is not included in the worktable.
Each sort performed for a merge join can use up to number of sort buffers for intermediate sort steps. Sort buffers for worktable sorts are allocated from the cache used by tempdb. If the number of pages to be sorted is less the number of sort buffers, then the number of buffers reserved for the sort is the number of pages in the worktable.
