Foreign Keys with JOIN operation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Foreign Keys with JOIN operation

Author

Topic

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 04:25:03

Hi,

can anybody tell me "How Foreign Keys reduce overhead from JOIN operation ?"
Do we have any post/white papar/blog on it ?

venkat09
Starting Member

17 Posts

Posted - 2010-03-02 : 06:11:29

Take tables lets say:

Products (Id int, Name nvarchar(100))
Versions (ProductId int, VersionNo nvarchar(10), ReleaseDate datetime)

Products:

Id Name
1 AutoCAD
2 AutoDesk CAD
3 Studio Max CAD
4 TouchStone Movie Maker

Versions
ProductId VersionNo ReleaseDate
1 1.1 2/2/2009
1 1.2 4/2/2009
2 1.0 1/1/2009
3 2.8 12/31/2009
4 8.0 1/1/2010
5 8.1.0 2/1/2010

If you have a foreign key constraint linking Products.Id and Versions.ProductId, you cannot enter records in Version for which no product exists. Also, you cannot delete a record from Products unless all the records of Versions are deleted.

So, a query like

select Products.Name + ' ' + Versions.VersionNo as Product, ReleaseDate
from Products inner join Versions on Versions.ProductId = Products.Id
where Products.Name like '%CAD%'

The steps in executing the above query is,

1) a temporary table is created with all the columns in Products and all the columns in Versions. This table has all possible permutations of a product with all the records in Versions without any condition.
i.e, for each record in products, all the records in versions are retrieved.

2) records are filtered from the temp table according to the condition.
3) only those columns (Product, ReleaseDate) will be selected.

But because you had foreign key constraint, nobody could tamper the tables with meaningless data (having versions for a product which doesnot exist, etc).
Hence, you get to deal with fewer number of records in step one (the one with temp table), than that in the case if you didnot have the foreign key constraint and there's junk data in either of the tables.

There you actually got a lesser possible overhead.

Venkat R. Prasad

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 06:44:13

thanks

but can u give me explanation/example where i can find difference in performance (in execution plan)
while using foreign key or not using it ?

actually based on your example posted above, i found no differnce in foreign key and removing foreign key.

---Bhuvnesh-----
While(learning Sql...)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-02 : 08:36:39

There is no inherent performance gain other than usually an index is created to help enforce the constraint. You could create an index without the constraint and that would achive the same result. Documenting the constraint may give the optimiser extra information that it can use to enhance the query plan but generally it's just good practice to properly define & enforce integrity in your data model. There is actually nothing to stop you joining tables with no formally defined relationship.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources