Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
               t2_col1(FK)
share|improve this question
add comment

1 Answer

up vote 7 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

CREATE INDEX IX_table2fk ON
              table1 (t2_col1, t1_col3)
              INCLUDE (any columns needed for the query)

OR

CREATE INDEX IX_table2fk ON
              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
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
    
Thanks for your answer... –  Harun Feb 22 '11 at 9:10
add comment

Your Answer

 
discard

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.