Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from serverfault.com Nov 18 '11 at 19:49

This question came from our site for professional system and network administrators.

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.