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:
- Choosing a good clustering key
- Creating my primary key
- Creating my unique keys
- Creating my foreign keys, and then manually indexing my foreign keys
- 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.