Should I Index My Foreign Key Columns?

Q: I’ve heard that it’s a good idea to index my foreign key columns. Is that true?

A: The simple answer is yes. However, I’d like to discuss this in more detail because people often follow this answer up with “Then why doesn’t SQL Server automatically create them for me?”

First and foremost, SQL Server has never automatically created indexes on foreign keys. I remember a phase in which I seemed to be asked “When did SQL Server stop putting indexes on foreign keys?” all the time. In fact, I wrote a blog post about it (“When did SQL Server stop putting indexes on Foreign Key columns?”). That post provides good information about why you should put indexes on foreign keys, but I’ll summarize it here by saying that you can reduce the cost of managing the relationship by having an index on a foreign key column(s), and you might help the performance for processing certain joins by having your foreign keys indexed.

Although you can help some joins with these indexes, I’m very glad that SQL Server doesn’t require them. I think it would be truly beneficial if indexes were automatically created when the foreign key is created, but I want to be able to drop them later. For example, I might replace some of them with slightly wider indexes (i.e., indexes that have more columns) that can be used for more than just managing the foreign key relationship. So I’m glad that indexes aren’t required on foreign keys. Ideally, I’d like SQL Server to give me the option to create the indexes when I create the foreign key constraint, but still allow me to drop them independently from the foreign key constraint.
Indexing basics (for me) include the following:

  1. Choosing a good clustering key
  2. Creating my primary key
  3. Creating my unique keys
  4. Creating my foreign keys, and then manually indexing my foreign keys
  5. Creating any other indexes needed for highly selective criteria (on which I’m often searching)

Once those steps have been performed, I can start to do workload analysis to create additional indexes.

 

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×