Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.
I've got a product history table that is inserted into frequently (never updated) and the table has this kind of structure
- ProductNo String(20)
- CreatedDateTime DateTime
- Description String(100)
At the moment the primary key is made up of a combination of ProductNo and CreatedDateTime in an attempt to define a unique index key. We can have many records per productno.
I'll be creating some 1 to 1 related tables and don't want to carry both the productno and the createddatetime fields into the related tables to act as foriegn keys. I also think this combination is a little fragile in order to guarantee uniqueness.
So, I'm planning to add a new field to the table 'ProductHistoryPK' as an incrementing Int or SequentialGuid to act as the primary key and a foreign key to related tables.
In terms of indexes I'm thinking of creating
- Non-clustered primary key on the new ProductHistoryPK field.
- Clustered Index on the ProductNo field as this is field that is often searched on.
Any thoughts or pointers regarding this?