CODEWITHSUNDEEP
sql-serverindexing
hazimdikenli
hazimdikenli

Reputation: 6029

should primary key be included in regular indexes

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?

Upvotes: 0

Views: 339

Answers (4)

StuartLC
StuartLC

Reputation: 107387

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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135171

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

Upvotes: 7

gbn
gbn

Reputation: 432667

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.

Upvotes: 1

bjorsig
bjorsig

Reputation: 1107

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.

Upvotes: 0

Related Questions