2

What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns?

I have come across a table that has a unique non clustered index as a GUID but the clustering index is applied on the combination of 9 columns. In total the table has 29 columns.

Can anyone help me understand what might be the benefits of having such a clustering key? As per Kimberly Tripp || Index Debate by Kimberly Tripp the clustering Index should be narrow, unique and static, but this case is totally opposite to what Kimberly states:

  1. the primary key is susceptible to changes so its not static
  2. It is definitely not narrow as it spans multiple columns and other non clustering indexes refer to it for mapping. so it is definitely expensive for IAM/PFS.
  • 3
    9 TINYINT columns are narrower than 1 GUID. Can you share the CREATE TABLE? – ypercubeᵀᴹ Nov 8 '16 at 11:20
  • 3
    Is this a data warehouse or OLTP system? – mendosi Nov 8 '16 at 12:11
2

A narrow, static, unique clustering key is better in situations like these:

  • The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
  • The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
  • No other field or combination of fields is guaranteed to be unique
  • Queries against the table usually retrieve one row and use a surrogate key to do so (e.g. because the query comes from an application that has learned the surrogate key, not a user who is searching by natural key)

A multi-column clustering key is better in situations like these:

  • No other table references this one (e.g. a fact table in a data warehouse)
  • Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
  • There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
  • A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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