Is joining on FK faster than joining without FK?

Question

Let's say I have two tables: a and b :

 a { pk as int fk as int ... } b { pk as int ... } 

I want to join a and b in a query like this:

 FROM a JOIN b on a.fk = b.pk 

Which of the following scenarios will be faster?

• a.fk configured as a foreign key on b.pk - b.pk indexed
• a.fk configured as a foreign key on b.pk - b.pk not indexed
• there is no relationship between tables - b.pk indexed
• there is no relationship between tables - b.pk not indexed

Bonus question - how much faster / slower will each of these scenarios be?

If you could back up your answer with a link, that would be awesome. Thanks!

Answer 1

Best practice

• Foreign keys are a tool for relational integrity , not a tool for performance. You should always create indexes in FK columns to reduce the search. SQL Server does not do this automatically.
• As stated here Foreign keys improve performance

Logically, this gives the following rating score:

• a.fk configured as a foreign key on b.pk - b.pk indexed
• there is no relationship between tables - b.pk indexed
• a.fk configured as a foreign key on b.pk - b.pk not indexed
• there is no relationship between tables - b.pk not indexed

Answer 2

The differences in performance differ from each other in indexed and non-indexed versions, however, whether it will be faster or slower depends on whether it was a choice or an insert. The presence of indexes and foreign key constraints slows down inserts, but speeds up selection (index) or makes data more reliable (FK). Since in most cases, most inserts have slowed noticeably (unless you are doing large volume inserts), you usually have FK and an index in your interest.

Answer 3

I will answer Liven. To answer your bonus question about how much of the productivity gains you get from creating the index, the answer is "It depends."

If one or both tables are small and they are the only two tables in the query, the performance gain can be small to zero. When the number of records is small, sometimes it’s faster to just read all the records and not use the index in any case. The database engine must be smart enough to understand this - that "query optimization is everything."

Similarly, if you have other tables involved and other selection criteria, the database engine may decide not to use this index and that another way to search for records is faster.

In another case, if you have two very large tables, creating an index in the field used to combine them can reduce the execution time by 99% or more.

That is why it is a good idea to learn to read the explanation plans for your database engine. If the request takes a long time, run an explanation plan and see what it does. Often creating a good index can greatly improve the query.