Composite or Single-Field Clustering Key

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 Detail like:

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 JOIN Detail and Header on ClaimId as well.

For the Detail table, which would be preferable for the clustered index key:

ClaimId

or

ClaimId, LineNumber

ClaimId alone is NOT unique, but the combination of ClaimId, LineNumber is unique for a Detail record.

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 LineNumber.

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 JOIN condition.

This is bollocks:


... ClaimId alone is a better clustered key because it is narrower

because of this


ClaimId alone is NOT uniquej

A non-unique clustered index will add a 4 byte uniquifier to remove ambiguity of ClaimId because it is the clustered index. Why? One reason is all NC indexes refer to it: so how to know ClaimId is which?

It was demonstrated (some time ago, maybe not valid now and can't find it) that non-unique clustered indexes break when you exhaust 2^32 values of the 4 byte uniquifier

Edit :

Question states ClaimId is not unique so assumed that uniqifier exists. No need to comment that it may not exist in the context of the question

StackExchange Database Administrators Q#9215, answer score: 9