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'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.)

share|improve this question
    
stackoverflow.com/questions/507179/… –  bummi Dec 28 '12 at 10:10
1  
Is it also missing indexes on the join columns (the columns that would be the FK if any was defined)? –  Martin Smith 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

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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.

share|improve this answer
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

 
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.