Non-clustered index and clustered index on the same column

Question

I came across this post in Stackoverflow. The first answer mentions something like A clustered index has all the data for the table while a non clustered index only has the column + the location of the clustered index or the row if it is on a heap (a table without a clustered index). How can a non-clustered index have the location of the clustered index? It only contains the column values sorted as nodes in a B-treee with each node pinting to the row where the column has that node-value, right?

Answer 1

Assuming you're talking about SQL Server and also assuming that you have a clustered index on your table (as you should).

Then the nonclustered index has the columns that you define in your CREATE INDEX statement, plus it has the column(s) that make up your clustered index (if present).

That clustering key value is the "pointer" to where the actual data is located.

If the query executor seeks through your nonclustered index for a value and find a match, then

• either that value is all you care about - then you just get back that value

• or the nonclustered index might also have some included columns (in the leaf level page) and with those, the query can be satisfied (all columns requested are present) so you get back the values you asked for

• or then the values you want are not all in the nonclustered index leaf-level page (that's especially true if you do SELECT * all the time) and then the query executor has to take the clustering key value from the nonclustered index, and go back to the clustering index, do what's called a key lookup, seek through the clustering index, and find the associated data page where the complete row is stored -> and now the query executor can return the values you've asked for

For a pretty good explanation - see this blog post here. It says:

In NonClustered Index:
....
2.b. If the table has a Clustered index, or the index is on an Indexed view, the row locator is the Clustered index Key for the row. SQL Server retrieves the data row by searching the Clustered index using the Clustered index Key stored in the Leaf row of the NonClustered index.

Or see this blog post in a whole series on SQL Server indexes which explains the "bookmarks" stored in a nonclustered index leaf-level page, too.