Does the SQL Server clustered index replace the RID lookup "index"

Question

When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index?

For example if I have a table with a single non-clustered index (indexing one column) and search for a row via that column it will do Index Seek -> RID -> Data row lookup -> Result

But if I add a clustered index on a different column then the same query will do the following Index Seek -> Extract clustering key -> Clustered index seek -> Results

This implies to me that the non-clustered index no longer 'terminates' with a RID at the leaf but with a clustering key of the clustered index? Is that right?

Answer 1

Yes, you got it pretty much figured out.

When you have a clustered index, then any non-clustered index will also include the column(s) from the clustered index as their "lookup" into the actual data.

If you search for a value in a non-clustered index, and you need to access the remaining columns of the underlying data, then SQL Server does a "bookmark lookup" (or "key lookup") from that non-clustered index into the clustered index (which contains the data itself, in the leaf-level nodes). With a clustered index, you don't need the RID's anymore - and thus you don't have to update all your index pages if a RID changes (when data gets moved from one page to another).

Bookmark lookups are rather expensive operations, so you can add additional columns to your non-clustered indices via the INCLUDE statement. With this, your non-clustered index will contain those additional columns on its leaf-level pages, and if you only need columns that are contained in that set of data, your query can be satisfied from the non-clustered index itself (in that case, it's called a "covering index") and you can save yourself a series of bookmark lookups.

Answer 2

No. Not every query will use the clustered index. If a query is "covered" by a nonclustered index (all the columns required for the query are contained in the NC index) then SQL Server will only need to read those index pages and not perform the bookmark lookup. In fact the optimizer will often prefer to use a covering NC index whenever it can because the NC index is usually smaller than the clustered index and is therefore usually faster to scan.