Indexes on Foreign Key Columns

[bperry]

Indexes on Foreign Key Columns
(long but interesting)

Most readers of this forum are well aware that SQL Server does not automatically create any indexes on Foreign Key columns that reference another table. (Although an index is automatically created on a Primary Key column.)

In this small example, an index will be automatically created on both PkId columns, but not on the FkId column in the EmpHours table.
[tt]
CREATE TABLE Employees (
PkId int Primary Key,
EmpName
)
CREATE TABLE EmpHours
PkId int Primary Key,
Hours dec(4,2),
FkId int Not Null REFERENCES Employees (PkId)
)
[/tt]
The main purpose of the FkId column is to prevent EmpHours rows from being entered that do not have a matching row in Employees. And we also know that it is an excellent idea to create our own indexes on those FK columns (even though SQL Server didn't) to support queries where we are joining tables together by those FK columns. i.e.

Select *
from Employees e INNER JOIN EmpHours h
ON e.PkId = h.FkId

Every book you care to pick up will point out that the performance of our queries will suffer unless there is an index on the EmpHours.FkId column.

However (and I'm finally getting to the point here!), what is not so obvious is that SQL Server will also use our index on FkId to do its own checking for referential integrity. If we delete a record from Employees, SQL will first check before allowing the deletion to make sure there are no related child records in EmpHours. But SQL will use the index we created to do that! And if we did not create our own index on FkId, then SQL will likely do a table scan to find any matching records.

In other words, when enforcing referential integrity, SQL doesn't have any magic bullets or secret backdoors to find out if any records match. It must use the same access methods we use: if we don't create indexes on foreign keys, then it must do table scans to enforce integrity, and performance will be adversely affected (esp. if the child tables are large.)

I've looked at a number of books for this kind of comment, but I've only seen one that points out the situation:

"If you do not index your foreign key column(s), you are likely to experience bad performance if you delete a row from the parent table, since a table scan will probably be performed on the child table."

(Ken England, p. 367, MS SQL Server 2000 Performance Optimization and Tuning Handbook)

So I guess the lesson is to make sure we create indexes on our foreign key columns, but not only just to improve the performance of Joins in our queries. Even if we don't plan on writing any queries, it turns out that it's still a good idea to create the FK indexes, because SQL will use them when enforcing referential integrity.

(That's it. Did I lie when I said this was interesting?)

bperry

 

[bperry]

As if that wasn't long enough, here I am adding to it...

I felt I should have also mentioned that when creating the index on the Foreign Key in the child table (i.e. in this case FkId in table EmpHours), quite often this new index should be the clustered index in the table.

That's because when we join to the table, we are often using a Where clause to extract a particular group of rows (i.e. Employee 1234). So when that occurs, SQL can retrieve that employee's records more quickly if the rows are 'clustered together' in the same data pages(s).

Of course, it's hard to make general statements, and everything depends on the specific situation. Sometimes the PK in the table should be the clustered index, but it's worth looking at the FKs in the table to see if one of those would be better.

Many readers in this forum would already be quite aware of this, so I'm just adding this note for completeness sake.)

bperry