Index on foreign key or not in SQL Server 2008

Question

Is there any need to add an index to a foreign key in SQL Server 2008 or is this handled by default. In many of my tables I have one FK that points to the user account table and most selects are made with this WHERE Account_FK = id. So index could be a quick performance win here i hope.

Answer 1

As a rule, you want all your JOIN keys to have indexes on them, so yes add an index.

If it's a composite key make sure to put all the relevant fields in the index key list in an appropriate order.

To my knowledge the only times an index is created automatically in SQL Server is when you add a primary key to a heap (non-indexed table) - the PK is assigned as the Clustered Index Key automatically; or, as Damien points out below, when you add a UNIQUE constraint to a field or set of fields.

Answer 2

One not so obvious reason to add an index to the FK is for when you want to delete a row in the master table (your user account table). SQL Server does a check for every FK relation to see if there are any rows that would prevent the deletion, and that check is a lot quicker with an index on the FK column in the child table(s).