Sql-server – Does using PK and FK effect performance in negative way?

clustered-indexclustered-primary-keyforeign keyperformancesql server

Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up more memory. Any insight?

Best Answer

You should tag which database system and version you're referring to, as this could affect the answer that is appropriate. But assuming you mean one of the main modern relational database systems, there's a couple of different objects you're asking about, so here's an answer for each of them:

Primary Keys:

They do not affect performance in a negative way at all, and in fact can slightly help performance in certain database systems because they guarantee uniqueness on a set of fields in the table. It is bad relational design in most cases to not define a primary key on a table. Primary keys are not the same thing as clustered indexes, though some database systems will create the clustered index on the primary key fields by default, out of assumption the primary key fields will be used in predicates (JOIN, WHERE and HAVING clauses).

Foreign Keys:

Similar to primary keys these also have no measurable negative performance impact, and can also potentially help performance by guiding the database system to generate a more efficient execution plan in specific cases. (I find this to be a rare occurrence though and most times there's no performance difference one way or the other.)

Clustered Indexes:

The clustered index on a table can definitely improve performance when the fields of that index are commonly queried on in a predicate, and in most cases it's recommended to have one on a table. The clustered index is the logical ordering of the data in the table itself and therefore doesn't take up any additional space. Without a clustered index the table is logically stored in a heap (or similar data structure, depending on database system) which can be inefficient to read data from. A clustered index on a table can result in INSERT operations taking slightly longer, but the difference of generally milliseconds for INSERT operations is usually worth the tradeoff for the major performance improvements of SELECT, UPDATE, and DELETE queries.

The edge cases where there might be a net loss in performance from using a clustered index is when you use a staging table to temporarily store the data before it's moved elsewhere, and you don't plan to do any querying against that table other than INSERT operations or selecting the entire table.