For some types of queries, such as those that require the results to be ordered or displayed in groups, the SQL Server query optimizer might determine that it is necessary to create its own temporary worktable. The worktable holds the intermediate results of the query, at which time the result rows can be ordered or grouped, and then the final results selected from that worktable. When all results have been returned, the worktable is automatically dropped.
The worktables are always created in the tempdb database, so it is possible that the SA might have to increase the size of tempdb to accommodate queries that require very large worktables. Because the query optimizer creates these worktables for its own internal use, the names of the worktables will not be listed in the tempdb..sysobjects table.
Worktables will always need to be used when a query contains a GROUP BY clause. For queries involving ORDER BY, it is possible that the ordering can be done without a worktable. For example, if there is a clustered index on the column(s) in the ORDER BY clause, the optimizer knows that the rows are already stored in sorted order, so a sort in a worktable is not necessary (although there are exceptions to this, depending on the sort order installed on the server).
Because the data is not stored in sorted order for nonclustered indexes, the worktable will not be necessary if the most efficient access plan uses the nonclustered index. However, if the optimizer determines that scanning the entire table will require fewer I/Os than using the nonclustered index, a worktable will need to be created for the ordering of the results.
SELECT type, AVG(advance), SUM(ytd_sales) FROM titles GROUP BY type
STEP 1 The type of query is SELECT (into a worktable) GROUP BY Vector Aggregate FROM TABLE titles Nested iteration Table Scan TO TABLE Worktable 1 STEP 2 The type of query is SELECT FROM TABLE Worktable 1 Nested iteration Table Scan
SELECT * FROM authors ORDER BY au_lname, au_fname
STEP 1 The type of query is INSERT The update mode is direct Worktable created for ORDER BY FROM TABLE authors Nested iteration Table Scan TO TABLE Worktable 1 STEP 2 The type of query is SELECT This step involves sorting FROM TABLE Worktable 1 Using GETSORTED Table Scan
SELECT * FROM authors ORDER BY au_id
STEP 1 The type of query is SELECT FROM TABLE authors Nested iteration Table Scan
In Query 3, notice that no worktable was created for the ORDER BY clause. This is because the authors.au_id column has a unique clustered index, so the data is already stored in sorted order based on the au_id value, and an additional sort for ORDER BY is not necessary. In Query 2, columns au_lname and au_fname have a composite nonclustered index. However, because the optimizer chose not to use the index, and due to the sort order on the SQL Server, a worktable was created to accommodate the sort.