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.
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)