Should each SQL Server foreign key have a corresponding index?

Question

Is it good to create an index for each foreign key in a SQL Server database?

Answer 1

Yes, this is good practice, see here: When did SQL Server stop putting indexes on foreign key columns? scroll down to "Are there any benefits of indexing foreign key columns? section

Answer 2

Every foreign key? No. If selectivity is low (i.e., many values ​​are duplicated), an index can be more expensive than scanning a table. In addition, in an environment with high activity (much more active insert / update / delete than query), the cost of maintaining indexes can affect the overall performance of the system.

Answer 3

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 column.

For example, if you have a table [User] (ID int, Name varchar (50)) and a table [UserAction] (UserID int, Action varchar (50)), you most likely want to know what actions a specific user is. For example, you will run the following query:

select ActionName from [UserAction] where UserID = @UserID 

If you are not going to filter rows by column, then there is no need to put an index on it. And even if you are worth it, only if you have more than 20-30 rows.

Answer 4

From MSDN: FOREIGN KEY Limitations

Creating a foreign key index is often useful for the following 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 data from related tables is combined in queries by matching a column or columns in the FOREIGN KEY constraint of one table with a primary or unique key column or columns in another table.