We have table where rows unique between 3 columns. GroupA, GroupB, ProductID.

GroupA & GroupB are used in combinations to Group sets of ProductIDs and will always be used to lookup sets of productIDs. Sometimes a ProductID will be used in in the query also.

I wasnt sure if there would be any performance gains from combining GroupA and GroupB into the same column so that the index is clustered.

   GroupA | GroupB | ProductID
   A1     | B1     | 1
   A1     | B1     | 99
   A1     | B2     | 1
   A1     | B2     | 99
   A2     | B1     | 1
   A2     | B1     | 99
   A2     | B2     | 1
   A2     | B2     | 99

Dual group method above OR Single group method below.

   GroupAB | ProductID
   A1-B1   | 1
   A1-B1   | 99
   A1-B2   | 1
   A1-B2   | 99
   A2-B1   | 1
   A2-B1   | 99
   A2-B2   | 1
   A2-B2   | 99

   GroupA | GroupB | GroupAB
   A1     | B1     | A1-B1
   A1     | B2     | A1-B2
   A2     | B1     | A2-B1
   A2     | B2     | A2-B2

Before considering performance you need to think about normalization. If you can interchangeably concatenate or not concatenate Group A and Group B then it sounds like B functionally depends on A and that means your table is not fully normalized. One example of a problem this could cause is if you don't currently have any products that fit into a given group and sub-group then you have lost the information that those groups and sub-groups even exist. If for example you delete the rows for A2-B2-1 and A2-B2-99 you have lost the information that A2-B2 is a group subgroup. You need to go back to understand the business rules here and if this is a possible use case you need to implement tables for product groups. This analysis must be done regardless of how you decide to cluster.

Once you have completed normalization and if you still have this table structure (say you now have a product group, product sub group, and a product group sub group table which parents your Group-Sub Group-Product table), you regardless NEVER want to concatenate columns into a single column. This concatenation breaks 1NF as now you have 2 different date element concepts in the same column. You would be unable to query for all products in a given sub group across groups.

From a SQL Server clustering perspective, if this table will always be queried by product group/sub group, and possibly product id, and that is it, you will want to create a clustered index on all 3 columns. That will best support your query as data in the table will always be ordered by the 3 columns and you can seek right to the group/sub group/product you want. If you make random value inserts into the table over time however you will need to tune the freespace you allow in the index as well as periodically reorganize/rebuild the index over time to maintain clustering order.

| improve this answer | |
  • Thanks. This helps me see that my idea of combining the groups was not a good idea. I will create it with multiple columns with a clustered index. – zeal May 29 '12 at 13:28

I don't think it would really matter. You can cluster on both without concatenating them:

CREATE CLUSTERED INDEX ixc_MyIndexName ON MyTable (GroupA, GroupB)

Which will accomplish the same thing but not require you to modify your data.

| improve this answer | |
  • I would consider including ProductId as well. – dezso May 24 '12 at 15:00
  • @dezso Depends on his use case. I was merely answering his question, not recommending a best index for the table. – JNK May 24 '12 at 15:01
  • Of course you didn't, but the keywords 'index', 'clustered', 'unique' and 'performance gain' made me note that :) – dezso May 24 '12 at 15:05
  • 1
    yes, he should create unique index (GroupA, GroupB, ProductID), it enforces the constraint and also speeds up lookup. – Imre L May 24 '12 at 15:39

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.