Any reason to set a primary key of a table if the identify column is "covered" by a composite index? Is a separate PK pure waste? Or does SQL Server smart and optimize away the PK anyway?

Background: If I define a table index for columns A, B, C, D, then the composite indexing of (A,B,C), (A,B) and (A) comes for free.

It dawned on me that if my identity column is included in another composite index then it may be redundant and a complete waste to set a PK as the identity column.  Is this true?

Using my example below, if I create a composite index of (PersonID,DepartmentID) then NO value is added by designating PersonID is the table's PK, right?

As a personal development convention I always set the main key/identity column as the PK.  If the above is true, I can go through my database and remove many PK designations without harm... and should save PK index space on disk and needless CPU cycles maintaining the PK.

Any comments?
create table Person (
    PersonID int not null,
    DepartmentID int not null,
    Name varchar(50)

Open in new window

Who is Participating?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
A primary key is a unique index. As long as your composite index is unique, but not because of the combined fields, there is not much you gain. However, you can create the PK as clustered index, so the physical data structure is aligned to it.

MSSQL will not use redundant indexes if they are without value. But they need to be maintained on writes (insert, delete, update of key fields), which involves more effort.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Keep in mind a (A,B,C) index is bigger than a (A) index. If the difference is big, the gain will get significant.
Aneesh RetnakaranDatabase AdministratorCommented:

One of the cons of creating a PK as a composite column is the page splitting, i assume that you are considering a Clustered Indexed Primary key, in that case it can cause lots of page splits .
But since the identity column is sequential, that wont usually make any page splits.If you have a datetime column which never get updated, it can be used to form a composite pk instead of an identity column
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Another thought to consider is that, if you have the composite key of A, B, & C as your Unique Index/Key (or your PK), then every time you want to have an FK link to this table, you will have to have the values for columns A, b, and C in the other table, i.e. the one with the FK.  This may not seem like much of a strain until you start considering a) how many rows will be in the table with the FK and b) how many tables may need to have that FK.  
Having that composite key also means that every time an application/stored proc/whatever has to access a particular row, the entire composite key has to be provided.
development convention

1 always include/ensure you have a primary key for any permanent table
2 always specify the primary key


its always you choice as to what the clustering sequence for the table should be.. it doesn't have to be the primary key
and in many cases shouldn't be.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Lowfatspread is correct, of course. In cases like this, where the primary key column is sequential, a clustered PK does not make sense.
Lowfatspread is correct, of course. In cases like this, where the primary key column is sequential, a clustered PK does not make sense.
Excuse me but I beg to differ.  If your PK is steadily increasing, then, IMHO, it may well make sense to have it clustered.  Clustring on a steadily increasing PK will result in less fragmentation than clustering on, for instance, a key consisting of AccountNumber/PONumber/PaymentDate because that key will result in insertions between AccountNumber=1 and AccountNumber=2.
Now, if your PK is a NewIdentity (i.e. a GUID), I would say that you shouldn't cluster on it because a GUID is, essentially, a big ugly random number. and it will seriously fragment your table (thus presenting a case where the PK shouldn't be clustered ;-).
ZuZuPetalsAuthor Commented:
I didn't understand the answers very well.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.