Sql Server Indexes Include Primary Key?

Question

One of my co workers is under the impression that when adding an index to a table in SQL Server 2008 that the PK's index is added to that index as well. Therefore if you are using a wider primary key then that key will also be included in the new index vastly increasing the disk space used above and beyond the penalty already paid for the index on the PK. I hadn't heard that before and my searching so far is coming up empty.

Hopefully someone here can point me at relevant docs to confirm or deny this. Please?

Answer 1

Your co worker is conflating "Primary Key" with "clustered index key" (possibly because by default a PK created on a heap without specifying the nonclustered keyword will become the clustered index of the table).

It is true that on a table with a clustered index the value of the clustered index key will be added in as included column(s) to all non clustered indexes to act as the row locator. (though the column(s) won't be added in twice if they are already part of the non clustered index definition).

The ideal clustered index key is

• unique (To act as a row locator it must be unique - SQL Server will add a uniquifier in if it is not)
• narrow (As it is reproduced in all non clustered indexes)
• static (Avoid having to update the value in multiple different places)
• ever-increasing