nonclustered index on join

Question

I have been wondering if having nonclustered index on the join would improve performance?? like

INNER JOIN FaceList fl ON fl.FaceListID = flf.FaceListID

in above join can I get performance benefit when I have nonclustered index on FaceListID since its unique combination and return a definite result

Any link or suggestion is appreciated

**YES!** most definitely. Putting a nonclustered index on any foreign key columns is an accepted best practice. It helps because thanks to the index, the values in that column are presented to the query optimizer in a sorted way - so it can use more efficient merging operations. — marc_s, Mar 25 '13 at 06:57

score 3 · Accepted Answer · edited Apr 03 '13 at 09:14

3

You definitely need to put non-clustered index on foreign-key column:

1 Answers1