Post reply

Index on Foreign Key

  • May 27, 2009 at 8:10 am

    #221662

    Hi everybody,

    Can anybody shed some light on the usefulness of creating an index on foreign keys?

    I would definately go for creating a clusteredi ndex on the Primary key, and non-clustered indexes on query's Where clause filters.

    Thanks in advance

    Jon

  • May 27, 2009 at 8:26 am

    #1000651

    It might help, depends how the optimiser chooses to do the joins. Test some of your queries both ways and see.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 27, 2009 at 8:31 am

    #1000656

    I always add an index on a foreign key column. If you don't have an index on the foreign key, any join with the selection criteria on the parent table will force it to scan the child table.

    For example, if you have a parent table with 200 rows and a child transaction table with 20 million rows, a delete of a single row from the parent table will force a scan of the 20 million row child table to verify there are no matching rows if the child table does not have an index on the foreign key.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply