Should every SQL Server foreign key have a matching index?

The reason for indexing a foreign key column is the same as the reason for indexing any other column: create an index if you are going to filter rows by the column.

For example, if you have table [User] (ID int, Name varchar(50)) and table [UserAction] (UserID int, Action varchar(50)) you will most probably want to be able to find what actions a particular user did. For example, you are going to run the following query:

select ActionName from [UserAction] where UserID = @UserID

If you do not intend to filter rows by the column then there is no need to put an index on it. And even if you do it's worth it only if you have more than 20 - 30 rows.


Every foreign key? No. Where the selectivity is low (i.e. many values are duplicated), an index may be more costly than a table scan. Also, in a high activity environment (much more insert/update/delete activity than querying) the cost of maintaining the indexes may affect the overall performance of the system.


Yes it's a good practice, see here: When did SQL Server stop putting indexes on Foreign Key columns? scroll down to the Are there any benefits to indexing foreign key columns? section