Indexing Foreign Keys

Kendra Little answers a question about indexing foreign key constraints:

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!

Curtain down!

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.

Related Posts

Generating Index Drop And Create Statements

Drew Furgiuele says “Game over, man, game over!” to indexes: The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it: Creates a special schema to house a temporary object, Creates a […]

Read More

Index That Column Or Include It?

Jeanne Combrinck lays out her recommendations on whether to make a particular column part of an index or have it be an included column: The original question we wanted to answer was whether we would see a performance difference when a query used the index with all columns in the key, versus the index with […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930