Worktable created for REFORMATTING

When joining tables, SQL Server might, in some cases, use a reformatting strategy to join the tables and return the qualifying rows. This strategy is considered only as a last resort, when the tables are large and neither table in the join has a useful index. The reformatting strategy inserts the rows from the smaller of the two tables into a worktable. Then a clustered index is created on the worktable, and the clustered index is used in the join to retrieve the qualifying rows from each table.

The main cost in using the reformatting strategy is the time and I/Os necessary to build the clustered index on the worktable; however, that cost is still less than joining the tables with no index. If user queries use the reformatting strategy, it is generally a good idea to examine the tables involved and to create indexes on the columns of the tables being joined.

The following example illustrates the reformatting strategy. Because none of the tables in the pubs database are large enough for the optimizer to consider using this strategy, two new tables are used. Each table has five columns defined as char(200). Tab1 has 500 rows and Tab2 has 250 rows.

Query:

SELECT Tab1.col1
FROM Tab1, Tab2
WHERE Tab1.col1 = Tab2.col1

SHOWPLAN:

STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for REFORMATTING
FROM TABLE
Tab2
Nested iteration
Table Scan
TO TABLE
Worktable 1

STEP 2
The type of query is SELECT
FROM TABLE
Tab1
Nested iteration
Table Scan
FROM TABLE
Worktable 1
Nested iteration
Using Clustered Index