SQL Server and indices

Question

Is it beneficial to add an index to a column that is part of a foreign key relationship? I have two columns which will be queried frequently and already have them foreign keyed but wasn't sure if I should index them aswell, or if the foreign key creates an index behind the scenes?

Answer 1

SQL Server does not create a behind the scenes index, so creating an index on all foreign key fields is advisable to improve look up performance.

Details and additional benefits: http://technet.microsoft.com/en-us/library/ms175464.aspx

Answer 2

It is defenitely advisable to add an index to your FK column if you query it often.

In your situation, it is probably even better if you create a composite index which spans your 2 columns. This is only advisable however (composite index) , if you often execute queries that filter / order on these 2 columns. If you decide that a composite index is appropriate, then you should pay attention to the order in which you put an index on those columns.

Answer 3

Creating an index for your FK fields is strongly adviseable - this will almost definitely improve performance. Note that's for selects, inserts may be slower as the index itself will also have to be updated.

Sql Server will sometimes create an FK index 'on the fly' but that only exists for the lifetime of your query - you can look at the Sql Execution plan to see if that is occuring. Either way, creating your own index, under your control for FK fields is the way to go.