Sql-server – How tondex a JOIN result of two or more tables in order to improve the performance in SQL server

indexsql server

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)

Best Answer

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

Related Question