SQL Server 2017 Administrator's Guide

Non-clustered and clustered index cooperation

It's usual that a table has more than just one index, but a combination of indexes is often used. When a clustered index exists, leaf-pages of non-clustered indexes do not point to data pages addressing their records, but every non-clustered index key keeps association to a proper clustered index key.

In other words, when some non-clustered index is used in a query that needs, for example, all columns of selected records, non-clustered index key values are found and then SQL Server iterates through those values and searches the rest of the records over the clustered index.

This operation is known as key lookup and it works as a loop. Keys found in a non-clustered index are searched one by one ...