Index Foreign Key Columns?

December 21, 2014      Database Administration, SQL / Databases

In my experience, indexes are often misunderstood. Unfortunately, they have the largest impact on SQL Server's performance compared to any other database object.

Most decent database designers understand relational design and will create foreign key relationships between tables where needed. In this scenario, the Primary Key Table will have an index as the column on the left side of the foreign key is that table's primary key.

That leaves the Foreign Key Table. Should we index the column involved in the right side of the relationship? I argue that you absolutely should.

Why Is The Foreign Key Index Needed?

This index is required to prevent table scans in instances where lookups are performed on the foreign key table using that column. 

Consider this scenario: you have a primary key table of users with a primary key of UserID. In your other table, you are storing ModifyUserID to track who updates a row.

At this point no index is needed. But the data also isn't being queried using real-life scenarios.

Inevitably, someone will ask whether "Mary" modified a row, or a distinct list of who have modified rows. With an index on ModifyUserID, this query runs efficiently as index seeks are performed - not dreaded table scans.

Conclusion

This is just one scenario. And trust me when I say I have encountered similar situations many times over the years.

I have concluded this: if the data is important enough for a foreign key, then index the Foreign Key Table's column!