Who were the first creators of the Identity column? What problem were those creators originally trying to solve? Is there a story behind it?
closed as off-topic by LowlyDBA, McNets, Michael Green, Max Vernon, mustaccio Nov 29 '18 at 19:32
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, McNets, Max Vernon, mustaccio
Hi Bob and welcome to DBSE.
I assume (correct me if I'm wrong) that your question is not about the specific properties of SQL Server or Oracle IDENTITY, but about the use of IDENTITY as surrogate keys in general, which will be true for other implementations such as AUTO_INCREMENT, SEQUENCE, IDENTITY, GUIDs, OID as a design choice.
If that is what you mean, I salute you for this inquiry. Most people new to SQL never stop to ask these questions and just assume 'this is how it's done' because everybody seems to be doing it this way.
This is a controversial topic, and you will find people with strong opinions both ways. A quick search for "Natural Vs. Surrogate keys / artificial keys" will yield you enough reading material for years.
I also find this video session on the subject to be entertaining and educational.
I am of the opinion that both are valid solutions. However, I also see that surrogate keys are used indiscriminately by most, just because finding the correct keys and modeling it properly is a significant effort, that requires mastering the relational model, which is hard.
IMHO, that is unfortunately also the root cause of many data consistency, modularity, performance, coding, and manageability challenges that later end up very hard and very expensive to correct.
Ask anyone who has ever tried to merge two data sets that use surrogate keys. At that point, you have no choice but to look for the natural key. But, if the natural keys were never explored, or properly constrained with UNIQUE constraints, the inconsistencies and duplicates are typically in abundance.
In my designs, I find that I use natural keys for the majority of the tables, and carefully plan for the edge cases where a surrogate is beneficial. These design choice are very much a minority opinion.