20

In general, SQL Server uses B+Trees for indexes. The expense of an index seek is directly related to the length of the key in this storage format. Hence, a surrogate key usually outperforms a natural key on index seeks. SQL Server clusters a table on the primary key by default. The clustered index key is used to identify rows, so it gets added as included ...


15

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of ...


10

I believe, that the best lies in the middle. Natural keys overview: They are make data model more obvious because they are came from subject area, and not from somebody's head. Simple keys (one column, between CHAR(4) and CHAR(20)) are saving some extra bytes, but you need to watch for their consistency (ON UPDATE CASCADE becomes critical for those keys, ...


5

The main reason I support surrogate keys is that natural keys are often subject to change and that means all related tables must be updated which can put quite a load on the server. Further in the 30 years I have been using a variety of databases on many topics, the true natural key is often fairly rare. Things are supposedly unique (SSN) are not, things ...


4

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 mistake to attribute performance characteristics to keys. In this particular example however, two possible implementations of tables and queries are compared to ...


4

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" current requirements - a primary key that may (or may not) arrive in 5 years is not worth considering now! Surrogate keys are very widely used in practice, much ...


3

There is always at least something else you should also know and almost equally, always something else you should be consciously putting a stop to. Specifically in the context of data warehousing, which is a relatively fledgling sector, leveraging relatively new technologies. In regards to what I've seen in the real world, walking into a company for the ...


2

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. And also best to be as small as feasible, especially if there will be any FKs pointing to this PK. A major reason to avoid natural keys (outside of not having it ...


1

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 impact performance much. If you switch that value to INT, perhaps with ZEROFILL, the speed up will be small. If you add an INT AUTO_INCREMENT, then you are ...


1

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. Instead you're measuring performance of queries against two databases with notably different models, caused by the choice of their respective primary keys. What is ...


1

First of all, for 2000 records (as others have stated) everything will work. So, for the OP the natural key will still work. On the other hand VARCHAR fields for primary keys are (in most of the cases) a bad idea. They are inefficient, hard to index and provide slow performance. In most of the cases a numeric field ( int / bigint) will probably work better. ...


1

As a general rule every table ought to have a natural key because it usually isn't a good idea to duplicate information in a table. Only add a surrogate key if and when you find there is a reason to do so.


Only top voted, non community-wiki answers of a minimum length are eligible