Worktable size for sort-merge joins

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:

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.