Table Scan, Index Scan, Index Seek and RID/Key Lookup operators

Introduction

Currently studying for some Microsoft SQL Server exams and I ran into some articles about tablescan-, indexscan-, index seek- and RID/key lookup operators. I was looking for information about the differences between the types of executionplan operators. This blogpost is a description about queryplan operators regarding indexes.

Table Scan

Table Scan means that the whole table is scanned and every row is returned. Typically, this happens when there is no index defined on the table.

Clustered Index Scan

A Clustered Index Scan is when SQL server reads the whole index looking for matches.  Since a scan touches every row in the index, the cost is proportional to the number of rows in the table. This is only useful for small tables.

There seems some discussion on the Pinal Dave's blog about the whether the Index Scan is the same as a Table Scan. It seems to me that there are some slight differences but these can be ignored.

Clustered Index Seek

An Clustered Index Seek is where SQL server uses the B-tree of the index to seek directly for matching records. Clustered Index seeks are preferred for selective queries and this means that fewer rews are returned when this is used. General speaken the Clustered Index Seek step is used when the optimizer decides that the Clustered index can be used for the index seek otherwise a Index Scan is done (which is mainly the same as a Table Scan).

Non-Clustered Index Seek

Whereas the Clustered Index Seek retrieves records at the leaf levels of the B-tree, the non-clustered index has pointers to the actual data in the clustered index or table (heap).

Key Lookup

A Key Lookup happens when a index does not contains all the information to answer a query and the query optimizer decides to use the information of another index to complete the requested information.

RID lookup

Sames as Key Lookup but then the information of a table is used, instead of a index.

Conclusion

In my opinion the following operators can be ordered by best practice when optimizing your queries:

1. Clustered Index seek (in case of large selective tables).
2. Non Clustered Index Seek.
3. Key lookup.
4. RID lookup.
5. Index scan (may be useful for small tables)
6. Table scan

Greetz,
Hennie