My DBA and I are having a disagreement on index structure.
Consider data for a medical claim...
We have the
Header table with fields like:
ClaimId (varchar(50)), PaidAmount, MemberID...
and for each header we have one or more records in
ClaimId (varchar(50)), LineNumber (smallint), MemberId...
The efficiency of the structure or duplication of the data is outside the parameters of this question.
There are additional tables that tie back to individual
Detail lines by
ClaimId, LineNumber, and we frequently
ClaimId as well.
Detail table, which would be preferable for the clustered index key:
ClaimId alone is NOT unique, but the combination of
ClaimId, LineNumber is unique for a
One of us believes that
ClaimId alone is a better clustered key because it is narrower, and that the lookup will be just as efficient since we need to know the
ClaimId before we lookup the
The other believes that the combination of the two is better because it removes the need for an additional
RowID, and can be used in
JOINing to the support tables that need the
LineNumber as a