We recently had a SQL Server performance assessment. It remarked on two things that made me think:
1 ) tables with foreign keys but missing supporting index
2 ) tables with indexes that are not used
But in our case the remark in Case 2 was on a index that supports a foreign key!
Now to my question, in which cases should you as a rule create indexes to support a foreign key?
I think Kendra elaborates the pros and cons well. I’d lean against automatically creating indexes because I’ve worked with scenarios in which you don’t drive from the Parent to the Child; instead, you drive from Child to Parent or Something to Child to Parent and those indexes go unused.