All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
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 (
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.)
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
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂