Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Question Unanswered: Should I index foreign keys?

    Hi all

    What is the general consensus regarding indexing of foreign keys in tables?

    I have a lot of queries that perform inner joins from the 'primary key' table to a 'foreign key' table. I was wondering if there are any special considerations to take into account.

    For example, I think in some RDBMS, foreign keys are sort of automatically assigned indexes? Is there anything that SQL Server automagically does in the background when a foreign key constraint is created that may affect whether something should be indexed?

    Thanks
    Matt

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    It depends, if you have a lot of queries that join by the FK then I would put a nonclustered index on it.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    SQL Server does not assign an index to an FK column. and there is no consideration given the column if not indexed.

    i usually non-clust-index my FK columns

    most database vendors suggest this practice due to the algorythms being used for the query process
    SQL Server benefits highly from this by performing MERGE Joins.

    Merge joins are performed when two tables are being joined by columns that are presorted.
    if you non-clust-index the fk column, it would already be pre-sorted and a good candidate.

    however to negate this statement,
    Microsoft also suggests that if the query optimizer detects that the join you are perfiorming could benefit from a merge join then the optimizer will actually sort the columns for you anyway..

    in an additional note, if you dont want an additional index on the table you can create statistics for the columns that you search without actually creating another index.
    there is a mainenance factor however, be advised.
    Last edited by Ruprect; 03-15-04 at 02:14.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As you've got most of it from above replies, the other option is to run PROFILER during most used queries and submit trace to index tuning wizard for index recommendations.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Thumbs up

    Thanks all for clearing that up for me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •