0

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value.

I know some advantages, for example that there is no need to identify unique columns without NULLs for primary keys, and there is no need to manage composite primary keys, normal forms are easier to manage, and uniqueness is guaranteed.

I want to know, is there any good reason regarding to performance, or index structures, etc. that should make us use real world factful keys instead of surrogate ones?

Thanks.

closed as too broad by Akina, Erik Darling, Joe Obbish, hot2use, mustaccio Mar 20 at 12:39

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

5

There are many disadvantages to using surrogate keys, the most important ones IMHO are:

  1. Using natural keys significantly reduces the number of joins that your queries need to perform. With a surrogate key, you will always have to join back to the original table to get meaningful values.
  2. Surrogate keys 'abstract' the actual values users are looking for from the optimizer which, significantly hinders the usefulness of statistics.
  3. Natural keys enforce logical consistency and prevent duplicates. Surrogate keys are way too often abused as a magic solution, without regard to the candidate keys which in turn result in logical duplication of data.

While there are (rare) cases that justify the use of surrogate keys, the state-of-the-art is that 99.9% of the databases that i've seen just use this 'magic one-size-fits-all' solution, with detrimental impact on performance, modularity, and data consistency.

To learn some more about it, watch this session that I delivered on the subject at Silicon Valley Code Camp 2017.

0

If you can use existing column(s) as a natural key then your table will have one less column. Therefore SQL Server will have one less column to read.

However, on the other hand, if the natural key is referenced as a foreign key by another table and it is something long like social security number, it would have to be duplicated in the referencing table which could take up more disk, and cause slower joins.

  • 1
    There is no duplication anymore than than the 'duplication' of the ID. SSN is a 9 digit number. INT which is what most people use for ID is actually longer - it can store up to 10 digits... Both will require 4 bytes. and even if it was double the size, the benefits of maintaining data consistency, much simpler code, and way fewer joins FAR outweighs the little extra storage. you have to look at the whole picture, not just one minor aspect. – SQLRaptor Mar 21 at 15:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.