Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


A Foreign Key Optimisation
Published: Nov 27, 2020
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:

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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.