Indexing foreign key columns

Question

Is it a rule of thumb to put an index on all foreign key columns? It seems they would often be used in table joins and would beneifit from the index.

What if the FK column only has 2 or 3 possible values? For example if it was referencing a status table. Would it still be advisable to put an index on the FK field?

Answer 1

I would strongly disagree with Eelke - most SQL Server gurus will recommend to put a non-clustered index on foreign key columns, and I agree.

For the best summary of reasons and the best argumentation for those indices, see Kimberly Tripp's excellent blog post When did SQL Server stop putting indexes on foreign keys? - the answer is: never - it never did (that is one of many persistent urban myths - but it's still a myth).

Her core message is:

Are there any benefits to indexing foreign key columns? YES

• Better performance on maintaining the relationship on a delete of a primary/unique key. When you delete a key row, SQL Server must check to see if there are any rows which reference the row being deleted.
  o If the foreign key relationship is defined with NO ACTION (on update/delete) then a referenced row CANNOT be deleted as it would leave the referencing rows “orphaned.” To find the rows efficiently an index on the foreign key column helps!
  o If the foreign key relationship is defined with CASCADE (on update/delete) then when a referenced row is modified all of the referencing rows must be modified as well (either updated to reflect the new value or on cascade delete). To find the rows to modify efficiently, an index on the foreign key column helps!

• Better join performance - for many of the reasons above, SQL Server can more effectively find the rows to join to when tables are joined on primary/foreign key relationships. However, this is NOT always the “best” indexing choice for joins but it is a good start.

Answer 2

No it shouldn't be. Indexes should only be used when they are of use. Indexes only are of use when the table is of sufficient size say at least three blocks. That can easily be hundreds of rows for short rows.