A Foreign Key Optimisation
Published: Jul 12, 2026
I’ve had so many arguments surrounding foreign keys and why they’re there, whether they can be ignored and avoided and removed etc. Personally I’m a fan for the simple fact that I like a proper database with proper referential integrity enforced. But for those who think that’s not important, there are other reasons too and this happens to be one of them:
Let’s go back to a previous post in which I covered Semi Joins… these are when we’re returning data from Table A when at least one matching record exists in Table B:
However, there are cases when this isn’t necessary at all… for example, let’s flip the query round (and not use the Archive table):
Now, instead of the Semi Join we get the following:
How come? Well, it’s simple really… there is a Foreign Key relationship between the TransactionHistory and Product tables. This means that the Optimizer doesn’t have to consider any joins at all because it already knows that, by definition, all ProductIDs in TransactionHistory exist in the Product table.
This can mean a massive performance gain compared to a Join query and, if these are the type of query you use, this is another reason to keep foreign keys in your database.
Let’s prove that in a simple example:
As you can see, there’s a large difference between the two queries in terms of cost and efficiency… all due to the joys of a foreign key.
Let’s go back to a previous post in which I covered Semi Joins… these are when we’re returning data from Table A when at least one matching record exists in Table B:
select *
from Production.product p
where exists
(
select *
from Production.TransactionHistoryArchive t
where p.ProductID = t.ProductID
)
However, there are cases when this isn’t necessary at all… for example, let’s flip the query round (and not use the Archive table):
select *
from Production.TransactionHistory p
where exists
(
select *
from Production.Product t
where p.ProductID = t.ProductID
)Now, instead of the Semi Join we get the following:
How come? Well, it’s simple really… there is a Foreign Key relationship between the TransactionHistory and Product tables. This means that the Optimizer doesn’t have to consider any joins at all because it already knows that, by definition, all ProductIDs in TransactionHistory exist in the Product table.
This can mean a massive performance gain compared to a Join query and, if these are the type of query you use, this is another reason to keep foreign keys in your database.
Let’s prove that in a simple example:
if object_id('Production.TransactionHistoryCopy') is not null drop table Production.TransactionHistoryCopy
go
select *
into Production.TransactionHistoryCopy
from Production.TransactionHistory
go
select *
from Production.TransactionHistory p
where exists
(
select *
from Production.Product t
where p.ProductID = t.ProductID
)
select *
from Production.TransactionHistoryCopy p
where exists
(
select *
from Production.Product t
where p.ProductID = t.ProductID
)
go
As you can see, there’s a large difference between the two queries in terms of cost and efficiency… all due to the joys of a foreign key.