[SQL] Does Foreign Key improve query performance?

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?
   

Continue reading...