Does Foreign Key improve query performance?

sql sql-server performance indexing foreign-keys
Chaowlert Chaisrichalermpol picture Chaowlert Chaisrichalermpol · Feb 3, 2009 · Viewed 89.7k times · Source

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?

So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.

My questions are:

  1. Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?

  2. Should I create index on all FK columns (liked Products.CategoryId) in all tables?

Answer

cmsjr picture cmsjr · Feb 3, 2009

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.

Related questions

When should I use cross apply over inner join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I …

Read More
How to check which locks are held on a table

How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005

Read More
SQL Server SELECT LAST N Rows

This is a known question but the best solution I've found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I've a table with lots of rows. It is not a posibility to use that query because …

Read More