Foreign key indexing

Foreign keys are an essential part of a relational database. Foreign keys helps ensuring the data integrity at the logical level. However many time we forget to create proper indexes that support the foreign keys work effectively.

So is there any benefits of indexing the foreign key columns? 

It can give better performance when you delete or update a referenced row.  An index could also help you get better join performance too.

Lets see a scenario how an index can give better performance. In this demo, I will use the Person.Person from AdventureWorks database.

First and foremost thing that I will do is the data setup, It will take a while to setup the test data.

Lets create a table PersonType and insert a few rows.

CREATE TABLE PersonType 
( 
   PersonTypeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
   PersonType   VARCHAR(2), 
   TypeDesc     VARCHAR(30)
);

INSERT INTO PersonType
VALUES
  ('SC','Store Contact'),
  ('IN','Individual (retail) customer'),
  ('SP','Sales person'),
  ('EM','Employee (non-sales)'),
  ('VC','Vendor contact'),
  ('GC','General contact');

Now we will create a copy of table Person.Person in the dbo schema

SELECT BusinessEntityID BusinessEntityID, 
       PT.PersonTypeID  PersonType, 
       NameStyle, 
       Title, 
       FirstName, 
       MiddleName, 
       LastName, 
       Suffix, 
       EmailPromotion, 
       AdditionalContactInfo, 
       Demographics, 
       rowguid, 
       ModifiedDate 
INTO   Person 
FROM   Person.Person P 
       JOIN PersonType PT 
         ON PT.PersonType = P.PersonType ;

ALTER TABLE dbo.Person 
  ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED(BusinessEntityID); 

ALTER TABLE dbo.Person 
  ADD CONSTRAINT FK_Person_PersonType FOREIGN KEY (PersonType) REFERENCES 
  dbo.PersonType(PersonTypeID) ;

We have the data ready for our test, assume , we will delete a row from the PersonType table. This table contains just 6 rows and the delete should not take a long.
Let’s run the delete statement, of course, don’t forget to capture the execution plan.

DELETE p 
FROM   PersonType p 
WHERE  p.PersonTypeID = 10 ;

From the execution plan, Its evident that the table Person is being scanned to ensure that no rows are referring the PersonType that’s being deleted.

Things go worse when you have set  cascade rules on for delete or update. Here is the execution plan, its clear that scan operator is very costly.

Lets create an index on the PersonType and see if that helps the query perform better.

CREATE NONCLUSTERED INDEX NC_Person_PersonType
ON Person(PersonType)

Run the delete query once again and capture the execution plan. Now SEEK has replaced the SCAN operator which will help the query perform better.

Should I index all my foreign keys?

No.  Before adding an index,  carefully look at the activities around that table. Are there frequent update/delete operations on the table? If yes then an index might help. In other cases, the index will turn to be an overhead for the maintenance activities.

You can download the code used in the article here

References
When did SQL Server stop putting indexes on Foreign Key columns?