Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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.

share|improve this question

1 Answer

up vote 9 down vote accepted

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

share|improve this answer
For the sake of argument, how is that worse than having a 4 byte int as the second clustering key? – JNK♦ Dec 15 '11 at 13:57
2  
@JNK - If you are going to use this space anyway might as well be for something useful and makes your NCIs more covering. But how unique is ClaimId? The uniquifier only gets added when there are actually dupes. – Martin Smith Dec 15 '11 at 14:02
@MartinSmith It's definitely not unique. The nature of the data is we get multiple lines per ClaimID for the detail record, one per incident of service. So for an ER visit there would be a line for every service provided, but only one header record for the whole thing – JNK♦ Dec 15 '11 at 14:09
Do note that the uniquifier is only added when necessary. That is, as long as ClaimId is unique, no extra space will be taken. Depending on the cardniality of ClaimId, it may be perfectly fine to use it as the clustering key! Saturating the number of uniquifiers requires you to have 2^31-1 (SQL Server doesn't use unsigned integers in column types) non-unique ClaimId values, which I'd assume is highly unlikely. – Mark S. Rasmussen Dec 15 '11 at 14:25
It was stated that ClaimId is not unique. So this is the assumption made – gbn Dec 15 '11 at 14:28
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.