by Markus Winand.


This section explains the most common execution plan operations of Microsoft’s SQL Server database. You can also have a look at Microsoft’s documentation.

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 ROWID) to access a specific part of the index or table.

Index Seek, Clustered Index Seek

The Index Seek performs a B-tree traversal and walks through the leaf nodes to find all matching entries. See also Anatomy of an SQL 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 order by clause.

Key Lookup (Clustered)

Retrieves a single row from a clustered index. This is similar to Oracle INDEX UNIQUE SCAN for 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 SQL 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 Scan considerably, it is still one of the most expensive operations. Besides high IO rates, a Table Scan must also inspect all table rows so it can also consume a considerable amount of CPU time. See also Full Table Scan.

On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more at

Join Operations

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 by clause. 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 by clause. 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 by clause. 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.

Top-N Queries


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 Sort.

About the Author

Photo of Markus Winand

Markus Winand is the SQL Renaissance Ambassador. He is on a mission to introduce developers to the evolution of SQL in the 21st century. Markus can be hired as trainer, speaker and consultant via

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

The Use The Index, Luke! mug

Stickers, coasters, books and coffee mugs. All you need for learning.

Shop now

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license