1

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table:

Data from multiple databases will be consolidated in this table for the purpose of full text searching. Full text index will be on a single column.

I'm trying to decide on the best candidate for the primary (and the clustering) key. There will be two important fields: ID and Type. ID is an identity field (which is also primary+clustering keys) on the source tables. Using just this field will result in a conflict because several source tables have the same ID (identity) field. Type is a look-up table. Id and Type together is unique.

I came up with the following scenarios:

  1. Id and Type as primary+clustering key.
  2. Create an identity field on the target table (primary+clustering) as well as a ID (synced with source's identity field) and Type.
  3. Create a separate Id field on the target table (clustering). Primary key will be source's ID and type.

What would be the best choice here?

  • What data type is the Type field? – LowlyDBA Apr 9 '15 at 15:15
  • @John It will be int and a foreign key to a look-up table. – Stackoverflowuser Apr 9 '15 at 15:18
3

First I'm assuming that both ID and Type are INT columns. Second that this is going to be a somewhat active table with a fair number of inserts (and possibly deletes). And last but not least that you aren't going to be pulling a "range" of IDs. For example you don't need to look at ID 10000-15000.

If so I would create a third column that is an INT identity column. Make the Primary/Clustered key on it. Then create a unique key on ID + Type. By making the new column the clustered key you will avoid any fragmentation problems caused by inserting into the middle of pages and speed up your inserts. The unique key ID+Type will be used for your queries and will be small enough (8 bytes) that you shouldn't see any issues there.

Last but not least since you are trying to put a full text index on the table you will need a single column, non-null, unique key preferably an integer. The new column will fit that bill admirably. (And of course your ID + Type combo wouldn't work.)

  • Thanks for your reply. You would be really helpful if you could explain the points below: 1) I didn't understand the last paragraph at all. Why would I need an extra unique key (type int) beside Primary Key(which is an ID already as per your suggestion)? 2) "And last but not least that you aren't going to be pulling a "range" of IDs" - Why would that matter? What would be the downside of pulling a range of IDs? 3) "unique key ID+Type will be used for your queries and will be small enough (8 bytes) that you shouldn't see any issues there" - You mean a scenario exceeding 8KB page size? – Stackoverflowuser Apr 10 '15 at 8:09
  • 1
    1) The last paragraph is about the fact that you had stated you want to put a full text index on your table. That means that your primary key MUST be a single column preferably a integer. – Kenneth Fisher Apr 10 '15 at 11:50
  • 1
    2) Clustered indexes are very useful when doing range queries. If you were pulling ranges of IDs that might affect the decision as to what clustered index you use. 3) The size of the index is important. The smaller the index the better (as long as it's still useful). At 8 bytes wide you still have a very small index that will be highly useful. If it was a 200 byte index, for example, it would be far less useful. – Kenneth Fisher Apr 10 '15 at 11:53

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.