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.

Answer 3

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

No.

If a query only uses the fields covered by a secondary index and/or clustered index, the secondary index may (and most probably will) be preferred.

CREATE TABLE test (id INT NOT NULL PRIMARY KEY, value1 INT NOT NULL, value2 INT NOT NULL)

CREATE INDEX ix_test_value2 ON test (value2)

SELECT  value2, id
FROM    test

The query above will most probably use ix_test_value2, since it contains all the information the query needs, but is less in size.

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?

Yes, with some little corrections:

• If the clustered index is non-unique, the row pointer in the secondary index consists of the clustered key plus a special hidden column called uniquiefier (actually, this column is appended to the clustered index too).

• If the secondary index covers some of the columns of the clustered index,, only the missing parts of the clustered key are appended as row pointers.

• If the secondary index is declared UNIQUE, clustered key is appended only to the leaf-level records of the secondary index.