how can I index this? In a legacy system, I need to store (new table) and search on three possible natural key combos.
I'm thinking my new table will be:
declare @TableA table (pk int identity(1,1) primary key --.more columns ,fk1 char(4) not null ,fk2 char(2) not null ,fk3 char(3) not null ,fk_a1 char(5) null ,fk_a2 char(2) null ,fk_b1 char(6) null ,fk_c1 char(1) --more columns...
this new table needs to join to three different tables on these columns:
LegacyA: fk1, fk2, fk3, fk_a1, fk_a2 LegacyB: fk1, fk2, fk3, fk_b1 LegacyC: fk1, fk2, fk3, fk_c1
I'm thinking to add the following indices:
fk1, fk2, fk3, fk_a1, fk_a2 fk1, fk2, fk3, fk_b1 fk1, fk2, fk3, fk_c1
since I'll be searching and joining to LegacyA, LegacyB, LegacyC independently and separately, these indices should be used correct? Does this look like a sound approach?