Worktable n

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.

Query 1:

SELECT type, AVG(advance), SUM(ytd_sales)
FROM titles
GROUP BY type

SHOWPLAN 1:

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

Query 2:

SELECT *
FROM authors
ORDER BY au_lname, au_fname

SHOWPLAN 2:

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

Query 3:

SELECT *
FROM authors
ORDER BY au_id

SHOWPLAN 3:

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.