Index and Table Access
SQL Server has a simple terminology: “Scan” operations read the
entire index or table while “Seek” operations use the B-tree or a
physical address (
RID, like Oracle
access a specific part of the index or table.
- Index Seek, Clustered Index Seek
Index Seekperforms a B-tree traversal and walks through the leaf nodes to find all matching entries. See also “Anatomy of an. Index”
- Index Scan, Clustered Index Scan
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
- Key Lookup (Clustered)
Retrieves a single row from a clustered index. This is similar to Oracle
INDEX UNIQUE SCANfor an Index-Organized-Table (IOT). See also “Clustering Data”.
- RID Lookup (Heap)
Retrieves a single row from a table—like Oracle
TABLE ACCESS BY INDEX ROWID. See also “Anatomy of an. Index”
- Table Scan
This is also known as full table scan. Reads the entire table—all rows and columns—as stored on the disk. Although multi-block read operations can improve the speed of a
Table Scanconsiderably, it is still one of the most expensive operations. Besides high IO rates, a
Table Scanmust also inspect all table rows so it can also consume a considerable amount of CPU time. See also “Full Table Scan”.
Generally join operations process only two tables at a time. In case a query has more joins, they are executed sequentially: first two tables, then the intermediate result with the next table. In the context of joins, the term “table” could therefore also mean “intermediate result”.
- Nested Loops
Joins two tables by fetching the result from one table and querying the other table for each row from the first. SQL Server also uses the nested loops operation to retrieve table data after an index access. See also “Nested Loops”.
- Hash Match
The hash match join loads the candidate records from one side of the join into a hash table which is then probed for each row from the other side of the join. See also “Hash Join”.
- Merge Join
The merge join combines two sorted lists like a zipper. Both sides of the join must be presorted. See also “Sort Merge”.
Sorting and Grouping
Sorts the result according to the
order byclause. This operation needs large amounts of memory to materialize the intermediate result (not pipelined). See also “Indexing Order By”.
- Sort (Top N Sort)
Sorts a subset of the result according to the
order byclause. Used for top-N queries if pipelined execution is not possible. See also “Querying Top-N Rows”.
- Stream Aggregate
Aggregates a presorted set according the
group byclause. This operation does not buffer the intermediate result—it is executed in a pipelined manner. See also “Indexing Group By”.
- Hash Match (Aggregate)
Groups the result using a hash table. This operation needs large amounts of memory to materialize the intermediate result (not pipelined). The output is not ordered in any meaningful way. See also “Indexing Group By”.
Aborts the underlying operations when the desired number of rows has been fetched. See also “Querying Top-N Rows”.
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