8
votes
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
A key is a logical feature of a database whereas performance is always determined by physical implementation in storage and by physical operations run against that implementation. It's therefore a ...
- 3,797
6
votes
Accepted
How to get the "best of both worlds" with natural and surrogate keys? And could DBMSs be better?
While ypercube makes a good and logical point for the specific example of Countries, I would otherwise avoid using string-based data types because of the potential unexpected implications that can ...
- 38.6k
4
votes
Surrogate key vs Natural key
Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.
By the YAGNI principle, you should only code for "real-life" ...
- 30.3k
3
votes
Accepted
Good Natural Key For a Physical Mailing Address
As mentioned in the comments, free text fields make it tough to create a primary key on. And for something with as much variability as an address field, it's just not reasonably possible by itself.
...
- 38.6k
2
votes
Surrogate key vs Natural key
I think we should start out clarifying that we are talking about Primary Keys, not alternate keys, right?. And since we are talking about Primary Keys, it is best that they do not change over time. ...
- 69.4k
1
vote
coping with long natural string key
INT is 4 bytes. VARCHAR(12) is a little longer. I would gladly use either for a PRIMARY KEY without worrying about performance.
A BTree is efficiently structured such that a longer key does not ...
- 78.9k
1
vote
Natural Keys vs Surrogate Keys part 2
I think your question title, apart from the fact that it is not a question, is a bit misleading. You're not measuring performance of natural/surrogate keys, unlike the question you're referencing. ...
- 26.6k
1
vote
Foreign keys - link using surrogate or natural key?
Older question but still very relevant to data architecture. The debate to go surrogate or go natural is ongoing and of course it depends. Very good points made for both sides here and other questions ...
Only top scored, non community-wiki answers of a minimum length are eligible