Word TV

I recently had an occasion to research the impact of foreign keys on database performance. My assumption has always been that the benefits of data integrity provided by foreign keys was worth the performance cost. I general I believe this is still a valid assumption. However there are some situations were removing foreign keys or adding a NO CHECK option would make sense. For example, where IUD operations vastly outnumber read operations. In my experience this is rare. Usually joins and selects happen far more frequently. Obviously a profiling tool is necessary to make an informed decision. Also consider the end user’s expectations. Do they automatically expect an IUD operation to take longer than a read operation.

Since foreign keys are strictly for data integrity and not performance, I’ve made the assumption that foreign keys are also indexed. In a nutshell here are the advantages / disadvantages of foreign keys.

Advantages:

• Referential Integrity – aka no orphaned rows.
• FKs provide big hints to SQL Server’s query optimizer and to DB statistics, both of which lead to better performance.
• FKs give structure to the data, literally they are the relations in relational databases. In turn, this allows ORMs and visualization tools to see the structure of the database.

Disadvantages:

• Consistency must be checked on every insert, update and delete (IUD).
• The index the index must be updated on IUD
• Data has to inserted and deleted in a certain order.