SQL Server Index: One new table joining to three old tables, how to?

Question

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?

Answer 1

You could possibly just use the first index and "include" the fk_b1 and fk_c1 columns. It will depend on your data so you'll need to test, as mentioned previously.

Answer 2

That seems fine to me. But the only way to be sure is to implement and test.

Answer 3

You need to keep in mind that the indexes would be fine if they would be created on a temporary table, not on a table variable. On a table variable you cannot define statistics nor indexes, except for primary and unique keys.

So if you want indexes you need to mask them by a unique key. Details here.

Quote from Gail's article: "We can define as many unique constraints as desired on a table variable (up to the limit of number of indexes on tables). If the columns that need to be indexed aren’t unique themselves, we can always add the primary key column(s) to the unique constraint so that the combination is always unique."

PS: your choice for indexes seem fine. Look for the execution plan once you've set your queries to see if they are used.