I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign keys specified. Either the application takes care of all the business logic by itself, or the installation is wrong.

How much do foreign keys affect performance? Or do they exist only for business logic? Are there any cases when not having foreign keys are an advantage? (e.g. this system supports SQL Server, Oracle and DB2.)

  • 1
    Is it also missing indexes on the join columns (the columns that would be the FK if any was defined)? Dec 28 '12 at 12:50
  • @MartinSmith Yes I think so. The database has many tables and I have no real idea of how they join together so I can't swear it's true for all of them, but at least for some it is.
    – m__
    Dec 28 '12 at 14:21

Foreign Keys are a referential integrity tool, not a performance tool.

Foreign Keys are not automatically indexed and if they are not indexed this can cause performance problems at least in SQL Server

For more detail follow MSDN Article Improving SQL Server Performance

  • I don't understand why it was mark as answer and upvoted.Answeris very much incorrect.
    – KumarHarsh
    Oct 17 '18 at 9:34

The link provided in bummi's comment has useful information. Depending on what you're doing, FKs can improve performance because SQL Server's query processor can rely on there being matching data in a table.

In what context are you asking your question? Do you have a performance problem already, or are you being cautious?

It's better to add in the FKs and then disable them later (don't remove them, just disable) then to never have them at all.

  • 1
    It is important to note that foreign keys can only be used in this capacity if they are trusted. Meaning, if the foreign key was created or enabled with the WITH CHECK clause, and the check was successful. The NOT FOR REPLICATION clause will also render foreign keys "untrusted" on the subscriber side.
    – Matt M
    Dec 28 '12 at 13:15
  • @IanYates Yes, there are performance problems. Foreign Keys are just one thing that could be the problem...have already fixed index rebuild and statistics updates and such.
    – m__
    Dec 28 '12 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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