I am new to indexing and gone through the basics of indexing. I can find a default clustered index for the primary key constraint within the indexes part of the corresponding table, but after creating a foreign key constraint i can't find any.

Now i've a requirement in which indexing should be implemented to improve performance. I've read about indexing the foreign key in order to improve the performance of a JOIN result.

Do i need to add the foreign key column to an additional non-clustered index or the foreign key has a default indexing?

How can i effectively implement indexing if my SQL table structure is as follows and i've a JOIN query with WHERE clause using t1_col3

               table1                       table2
               ------                       ------
               t1_col1(pk)                  t2_col1(pk)
               t1_col2                      t2_col2
               t1_col3                      t2_col3
               t1_col4                      t2_col4

A foreign key does not have an index by default. You have to create one.

In this case, I'd suggest either of these. It depends on relative selectivity of the 2 columns

              table1 (t2_col1, t1_col3)
              INCLUDE (any columns needed for the query)


              table1 (t1_col3, t2_col1)
              INCLUDE (any columns needed for the query)

The INCLUDE will make the index covering to avoid key/bookmark lookups

  • 1
    Include was new feature in SQL-Server 2005. Just in case you are still on Sql-server 2000. – bernd_k Feb 22 '11 at 7:40
  • @bernd_k: Only 6 years and 3 versions ago :-) – gbn Feb 22 '11 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.