Nonclustered indexes contain a row locator back to the base table.
This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps.
So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot.
The row locator is added to the key for non unique nonclustered indexes and as an included column to non clustered indexes declared as unique.
There is no performance advantage of specifying this explicitly for the included
case. For the case that it is implicitly added to the key you may need to do this explicitly if your queries would benefit from a different asc
/desc
direction or multi column ordering than the implicit would give you.
You may prefer to be explicit about it for readability of your code for developers unfamiliar with the implicit behaviour or if your queries would still need those columns in the index even if the clustered index definition was to change in the future.
Regarding the followup question in the comments
Does that mean it's possible a key lookup might be performed from a
nonclustered index for the primary key (when the primary key is not
the clustered index) if that primary key is in the select list?
Yes, you can see this with the below.
CREATE TABLE #T
(
PK INT PRIMARY KEY NONCLUSTERED,
CI INT INDEX CIX CLUSTERED,
OtherCol INT INDEX NCIX NONCLUSTERED
)
SELECT *
FROM #T WITH (FORCESEEK)
WHERE OtherCol = 10

- The output list of the seek on
NCIX
is Uniq1001, CI, OtherCol
- The values of
CI, Uniq1001
are then used by the lookup to seek into the clustered index...
- ... and the value for
PK
is returned from that lookup.
If you were wondering about Uniq1001
this is a part of the clustered index key silently added by the system to act as a uniqueifier as the clustered index was not declared as unique. It is empty except for rows where duplicate exist/existed for the value of CI
.