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?
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)
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER