0

The database where I work has Table A with column A_id (an autoincrement field, which in practice is always unique, never null), and column Date (not unique), and other columns.

For some reason, the primary key/clustered index of this Table is a composite of columns A_id + Date. I think this is stupid, but before talking to my boss, I'd like to be sure.

If I understand correctly, the index is a Binary tree using A_id, and at each leaf, there's another mini Binary Tree that uses Date, with the actual data at each of its leaves.

But since A_id is already unique+not_null, then each mini Binary tree is simply an arrow pointing to exactly one leaf, making that part of the index totally superfluous.

I can think absolutely no cases whatsoever where such a composite pk/index could have any benefits over simply using A_id as the pk/index. There's absolutey no query that could be faster using this index than using simply A_id index.

Additionally, using this composite pk brings possible errors -you could manually insert a row with a repeated A_id, if it has a different Date, which is horrible- and that means every JOIN needs to include Date as well, to prevent joining with the wrong row: ON x.A_id=a.A_id AND x.Date=a.Date which is just annoying.

Also I suspect this index confuses the Query Planner, requiring me to add hints like OPTION (LOOP JOIN).

Am I correct to say that such composite PK/Clustered Index is strictly worse than a simple PK/Clustered Index using A_id alone?

4
  • 2
    I generally agree this is a bad primary key design from both normalization and query performance perspective. The use case where the composite clustered index key might benefit performance is the Date column will be implicitly included in all non-clustered indexes, which could allow non-clustered indexes to cover queries. Removing it without due diligence could result in performance regression of some queries.
    – Dan Guzman
    Commented Jan 10 at 13:35
  • A_id isn't guaranteed to be unique if IDENTITY_INSERT has been used or reseeding has happened. Have you validated that there are definitely no dupes? Commented Jan 10 at 13:38
  • 2
    The index isn't nested binary trees BTW - it is a single B tree with the whole key. All of the non leaf levels of the index will have rows of keys with two column (A_id, Date ) - but this will be unnecessarily bloated by having superfluous keys Commented Jan 10 at 13:38
  • @DanGuzman I didnt know about the possible benefit of covering non clusters, Ill have to learn more on that before advancing on this. #MartinSmith, no there are no dupes, for now
    – Juan Perez
    Commented Jan 10 at 16:59

1 Answer 1

3

Am I correct to say that such composite PK/Clustered Index is strictly worse than a simple PK/Clustered Index using A_id alone?

There's really two questions here:

The first answer regarding having a composite primary key, is probably generally worse, since as you pointed out, breaks down the key's integrity and allows the same A_id to be specified more than once then. Now, if you're inheriting this data, as Martin Smith points out, the data may currently not be unique on A_id anyway (despite being an identity) and should be thoroughly checked prior to removal of the Date column.

Secondly, the primary key does not need to be the clustered index. So it is possible to define A_id as the primary key but subsequently have a composite clustered index on (A_id, Date) instead. The potential benefit that could have is by helping cover your nonclustered indexes (as mentioned by Dan Guzman), since every nonclustered index also stores the clustered index key. If some of your queries leverage some nonclustered indexes, and those queries reference the Date field, then your nonclustered index likely won't need to explicitly keep a copy of the Date (via it's definition) since it'll already have one via the clustered index key it stores.

Converse to that, it's typically best to keep the clustered index key as light as reasonably possible, since it is copied into every nonclustered index. If you have no need for the Date field in any of your nonclustered indexes, then this is just unnecessary bloat in them. When applicable, an int identity column typically makes for an excellent clustered index key by itself.

So to the original question:

Is there any advantage in using IdentityColum+AnotherColumn vs just IdentityColum as the Clustered Index?

Yes, there can be, in regards to the clustered index key. It's not often the case though. Regarding the primary key, it makes less sense.

Also I suspect this index confuses the Query Planner, requiring me to add hints like OPTION (LOOP JOIN).

Less than likely, but no one can say without seeing the query itself and its execution plan. Having to use that kind of option is more of a red flag that your query needs tuning instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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