Does Foreign Key improve query performance?

Solution 1:

Foreign Keys are a referential integrity tool, not a performance tool. At least in SQL Server, the creation of an FK does not create an associated index, and you should create indexes on all FK fields to improve look up times.

Solution 2:

Foreign Keys can improve (and hurt) performance

  1. As stated here: Foreign keys boost performance

  2. You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.

Edit

As the link now seems to be dead (kudos to Chris for noticing), following shows the gist of why foreign keys can improve (and hurt) performance.

Can Foreign key improve performance

Foreign key constraint improve performance at the time of reading data but at the same time it slows down the performance at the time of inserting / modifying / deleting data.

In case of reading the query, the optimizer can use foreign key constraints to create more efficient query plans as foreign key constraints are pre declared rules. This usually involves skipping some part of the query plan because for example the optimizer can see that because of a foreign key constraint, it is unnecessary to execute that particular part of the plan.