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.
create table Person (
PersonID int not null,
DepartmentID int not null,