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.
SELECT Tab1.col1 FROM Tab1, Tab2 WHERE Tab1.col1 = Tab2.col1
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