2011-07-05Operations in the SQL Sever execution plan

The Index Seek performs a B-Tree traversal and walks through the leaf nodes to find all matching entries. See also Chapter 1, “Anatomy of an Index”.

Reads the entire index—all the rows—in the index order. Depending on various system statistics, the database might perform this operation if it needs all rows in index order, e.g., because of a corresponding order by clause.

Retrieves a single row from a clustered index. Similar to Oracle INDEX UNIQUE SCAN for an Index-Organized-Table (IOT). See also Chapter 5.

Retrieves a single row from a table—like Oracle TABLE ACCESS BY INDEX ROWID. See also Chapter 1, “Anatomy of an Index”.

Reads the entire table—all the rows—as stored on the disk. Although multi block read operations can improve the speed of a (full) table scan dramatically it is still one of the most expensive operations at all. Besides high IO rates, a table scan must also inspect all rows from the table so that it can also put a considerable load on the CPU. See also Full Table Scan.

Joins two tables by fetching the result from the first table and querying the second table for each row. SQL Server uses the nested loops operation also to retrieve table data after an index access. See also “Nested Loops” in Chapter 4.

The hash match join loads the candidate records from one side of the join into a hash table, which is probed for each row from the other side of the join. See also “Hash Join” in Chapter 4

The merge join combines two sorted lists like a zipper. Both sides of the join must be sorted by the join predicates first. See also “Sort/Merge Join” in Chapter 4

Sorts the result according to the order by clause. This operation needs large amounts of memory to materialize the intermediate result set (not pipelined). See also “Indexed Order By” in Chapter 6.

Sorts a subset of the result according to the order by clause. Used for Top-N queries if no pipelined execution is possible. See also “Querying Top-N Rows” in Chapter 7.

Aggregates a presorted set according the group by clause. This operation does not buffer the intermediate result, but is executed in a pipelined manner. If this operation is, however, preceeded by a Sort, that is not executed pipelined. See also “Indexing Group By” in Chapter 6.

Groups the result using a hash table. This operation needs large amounts of memory to materialize the intermediate result set (not pipelined). The output is not ordered in any meaningful way. See also “Indexing Group By” in Chapter 6.

Aborts the underlying operations when the desired number of rows was fetched. See also “Querying Top-N Rows” in Chapter 7.

The efficiency of the Top-N query depends on the execution mode of the underlying operations. It is very inefficient when aborting non-pipelined operations such as Sort.