Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
up vote 9 down vote accepted

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

share|improve this answer
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
Thanks for your answer... – Harun Feb 22 '11 at 9:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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