The sort operator has only one child operator within the query plan. Its role is to generate an output data stream from the input stream, using a specified sorting key.
The sort operator may execute a streaming sort when possible, but may also have to store results temporarily into a worktable. The sort operator displays the worktable’s name in this format:
Using Worktable<N> for internal storage.
where <N> is
a numeric identifier for the worktable within the showplan output.
Here is an example of a simple query plan using a sort operator and a worktable:
1> use pubs2 2> go 1> set showplan on 2> go 1> select au_id from authors order by postalcode 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). 2 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SORT Operator | Using Worktable1 for internal storage. | | |SCAN Operator | | FROM TABLE | | authors | | Table Scan. | | Forward Scan. | | Positioning at start of table. | | Using I/O Size 4 Kbytes for data pages. | | With LRU Buffer Replacement Strategy for data pages. au_id ---------------------- 807-91-6654 527-72-3246 722-51-5454 712-45-1867 341-22-1782 899-46-2035 998-72-3567 172-32-1176 486-29-1786 427-17-2319 846-92-7186 672-71-3249 274-80-9391 724-08-9931 756-30-7391 724-80-9391 213-46-8915 238-95-7766 409-56-7008 267-41-2394 472-27-2349 893-72-1158 648-92-1872 (23 rows affected)
The sort operator drains its child operator and sorts the rows. In this case, it sorts each row fetched from the authors table using the postalcode attribute. If all of the rows fit into memory, then no data is spilled to disk. But, if the input data’s size exceeds the available buffer space, then sorted runs are spilled to disk. These runs are recursively merged into larger sorted runs until there are fewer runs than there are available buffers to read and merge the runs with.
