Should every SQL Server foreign key have a matching index?
Solution 1
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
Solution 2
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.
Solution 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 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.
Solution 4
From MSDN: FOREIGN KEY Constraints
Creating an index on a foreign key 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 frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table.
Related videos on Youtube
![rohitwtbs](https://i.stack.imgur.com/zB7SW.png?s=256&g=1)
rohitwtbs
Software architect at multinational company. Focus on Microsoft platform.
Updated on July 05, 2022Comments
-
rohitwtbs about 2 years
What are the advantages, if any exist, of having an an index for every foreign key in a SQL Server database?
-
rohitwtbs almost 14 yearsOK. So is there some numeric limit or threshold that may trigger the creation of an index?
-
rohitwtbs over 11 yearsAre you sure that the index will not be useful during the foreign key validation when I execute an insert on UserAction?
-
ItsMe over 11 yearsNo. The validation is done against the table and column that holds the primary key or unique constraint (e.g. [User].[ID]). There is no need to validate the UserID column of the [UserAction] table. You can only reference a column that has primary key or unique constraint on it so it is always indexed.
-
Kenny Evitt almost 11 yearsIt's a good practice in some cases, but not for every foreign key.
-
T.J. Crowder almost 9 yearsThe other action to consider is deleting rows from
User
, which causes the engine to look for rows inUserAction
based onUserID
.