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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

Qlemo"Batchelor", 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.