12

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the keys in this table as foreign keys.

My question is which approach is more efficient for lookup speeds:

1) Do I create the new table including all 4 columns and reference them all in a foreign key.

or

2) Do I create a new identity column in the Primary Key table and use this as a foreign key in the new table.

This database is expected to hold a very large amount of data, so I have built it up until now with a view to minimising the amount of data held in each table. With this in mind, option 2 would be the best approach since I will save 2 int columns and a datetime column for every row, but I want to avoid increasing the lookup time if unnecessary.

Improve this question
1
  • 1
    I personally would almost always use a surrogate key (e.g. an INT IDENTITY) in such a case - makes referencing and joining that table just soooooo much easier. To avoid duplicates, put a UNIQUE constraint on those four columns. Also: narrow primary keys are much better for performance reasons (if they're used as clustering key)
    – marc_s
    Oct 3, 2013 at 14:13

2 Answers 2

Reset to default
11

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

  • As you point out, you'll have a much simpler FK relationship.
  • A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
  • If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.

Improve this answer
5

As so often in the SQL world, the answer is: "It depends."

Have a look at this question for some pointers: Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

There are cases that see a performance improvement when using natural keys as Foreign Keys. However, in most cases you are going to be better of with smaller keys (read: surrogate keys).

If you introduce that IDENTITY column I would even make it the Primary Key and change the "natural" columns to be a UNIQUE CONSTRAINT instead.

Improve this answer

Your Answer

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

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