Indexes on Foreign Keys?

[andreis]

Hi:

Is this a good idea to create indexes on all foreign keys (SQL 2000)? Some of my tables have 4 foreign keys which, if indexed, would leave little room for more indexes.

Thanks.

Andrei

 

[RickCole]

A FOREIGN KEY is generally a good candidate for an index.

You can also have up to 249 non-clustered indexs per table.

Rick.

 

[andreis]

Rick:

Thanks, but what if:

1. I have a heavily-inserted table. Then, as far as I know, more than 4-5 indexes would slow performance.

2. Foreign key is created on the field with only 10-15 different values? Would it be worth an index?

Appreciate your help.

Andrei

 

[RickCole]

Andrei

It's one of those where some times you have to try it out and see if your performance hit is worse then your gain. If you have the time have a look at and use the Index Tuning Wizard as this might help you in coming to a decision.

You are right in saying, that on a heavily inserted table that the more indexes you have then you may slow performance. If however you are using the foreign key to do a lot of queries then it might be worth the trade off.

However with only 10-15 values the query optimiser might easily make the decision to scan the table anyway so again the index might not be worth putting in.

Here are a couple of quotes from book on-line but in the end it's probably try it and see. If you don't have any performance problems at the moment then maybe the best solution is if it's not broken don't try and fix.

Rick.

Quotes from BOL:

A FOREIGN KEY constraint is a candidate for an index for two reasons:

Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
Foreign key columns are often used in join criteria when the data from related tables is combined in queries by matching the column(s) in the FOREIGN KEY constraint of one table with the primary or unique key column(s) in the other table. An index allows Microsoft® SQL Server™ to find related data in the foreign key table quickly. However, creating this index is not a requirement. Data from two related tables can be combined even if no PRIMARY KEY or FOREIGN KEY constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria. For more information about using FOREIGN KEY constraints with joins, see Join Fundamentals.

 

[andreis]

Rick:

Thank you very much, I'll take the approach you advised.

Andrei