should primary key be included in regular indexes

Question

Regarding MS SQL-Server, Is the Primary Key column of a table, included in the regular non-unique indexes on that table?

if not, would it make sense to include it in the index?

create table dbo.People (
PK_PersonId int not null,
PersonName nvarchar (100),
Bunch of Other Fields
,

so when creating an index on PersonName, would it make sense to add the Primary Key column PK_PersonId?

Answer 1

When you create a PK in SQL Server, by default it creates a clustered index on those column(s)

when you create a non clustered index it either points back to the clustered index or if you don't have a clustered index (your table is a heap) it points back to the table with a row locator

So in other words the non clustered index already has the PK value in it

Answer 2

A non-clustered index will refer to the clustered index anyway, not the actual data. By default, the PK is clustered in SQL Server.

So, no need to add it: it's there implicitly if PersonID is the clustered index as defined by PRIMARY KEY constraint.

Answer 3

If your PK is also the clustered index key (as per SQL Menace, the default), then the PK will be included on all non clustered indexes

http://msdn.microsoft.com/en-us/library/ms177484.aspx

Answer 4

if you are creating index to speed up name searches, then there is no point in making PersionId in the non-clustered index. The non-clustered index will refer to the record the same way as the clustered index so it is there anyway.