Is there a severe performance hit for using Foreign Keys in SQL Server?

by

There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than 2). This is a good thing not a bad thing as databases without integrity are untrustworthy and thus useless. You should not trade integrity for speed. That performance hit is usually offset by the better ability to optimize execution plans.

We have a medium sized database with around 9 million records and FKs everywhere they should be and rarely notice a performance hit (except on one badly designed table that has well over 100 foreign keys, it is a bit slow to delete records from this as all must be checked). Almost every dba I know of who deals with large, terabyte sized databases and a true need for high performance on large data sets insists on foreign key constraints because integrity is key to any database. If the people with terabyte-sized databases can afford the very small performance hit, then so can you.

FKs are not automatically indexed and if they are not indexed this can cause performance problems.

Honestly, I’d take a copy of your database, add properly indexed FKs and show the time difference to insert, delete, update and select from those tables in comparision with the same from your database without the FKs. Show that you won’t be causing a performance hit. Then show the results of queries that show orphaned records that no longer have meaning because the PK they are related to no longer exists. It is especially effective to show this for tables which contain financial information (“We have 2700 orders that we can’t associate with a customer” will make management sit up and take notice).

More Related Contents:

  1. can we have a foreign key which is not a primary key in any other table?
  2. how to create this table in sql server?
  3. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  4. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  5. What is the purpose of system table master..spt_values and what are the meanings of its values?
  6. How to create multiple one to one’s
  7. SQL – How to store and navigate hierarchies?
  8. Can a foreign key be NULL and/or duplicate?
  9. Add Foreign Key relationship between two Databases
  10. Does Foreign Key improve query performance?
  11. Why (and how) to split column using master..spt_values?
  12. SQL Server: Howto get foreign key reference from information_schema?
  13. How can I INSERT data into two tables simultaneously in SQL Server?
  14. Should I design a table with a primary key of varchar or int?
  15. Composite Primary key vs additional “ID” column?
  16. MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?
  17. Can I set ignore_dup_key on for a primary key?
  18. Should I use SQL_Variant data type?
  19. What does ON [PRIMARY] mean?
  20. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  21. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  22. Single or multiple databases
  23. Unpivot with column name
  24. How to convert DateTime to VarChar
  25. Foreign key referring to primary keys across multiple tables?
  26. SCOPE_IDENTITY() for GUIDs?
  27. Getting Dates between a range of dates
  28. How do I expand comma separated values into separate rows using SQL Server 2005?
  29. Why is 30 the default length for VARCHAR when using CAST?
  30. Can’t create stored procedure with table output parameter

Leave a Comment