Table Structure (SalesPersonProduct)
SalesPersonProductId INT (PK, Clustered)
SalesPersonId INT (FK - non-unique - Nullable)
ProductId INT (FK - non-unique - Nullable)
AreaId INT (Not Null)
DistributionType INT (Not Null)
ItemOrder INT (Not Null)
ItemSize INT (Not Null)
Scenario: SalesPersonId is not unique; in fact, this column may have 70 - 80 duplicates. However, this is the only column used for reading and joining and currently does not have an index.
SalesPersonProductId is just used as a PC for reference, updates for this table are processed through a PC, but are very rare. Neither PK nor SalesPersonId will be updated after adding a row to the table.
Question:
Suppose I cannot make any changes to the table structure or related tables. Would I be better at servicing changing an existing PC to a non-clustered index and adding SalesPersonId as a clustered index? OR just adding SalesPersonId as a standard non-clustered index?
I know that behind the scenes many unique values will get unique identifiers, however this column is used for almost all reads, and I expect this to provide better performance, but due to the extra bytes, they are now used to preserve "uniqueness" in this new cluster index .