Pros and Cons of autoincrement keys on "every table"

I'm assuming that almost all tables will have a primary key - and it's just a question of whether that key consists of one or more natural keys or a single auto-incrementing surrogate key. If you aren't using primary keys then you will generally get a lot of advantages of using them on almost all tables.

So, here are some pros & cons of surrogate keys. First off, the pros:

• Most importantly: they allow the natural keys to change. Trivial example, a table of persons should have a primary key of person_id rather than last_name, first_name.
• Read performance - very small indexes are faster to scan. However, this is only helpful if you're actually constraining your query by the surrogate key. So, good for lookup tables, not so good for primary tables.
• Simplicity - if named appropriately, it makes the database easy to learn & use.
• Capacity - if you're designing something like a data warehouse fact table - surrogate keys on your dimensions allow you to keep a very narrow fact table - which results in huge capacity improvements.

And cons:

• They don't prevent duplicates of the natural values. So, you'll still usually want a unique constraint (index) on the logical key.
• Write performance. With an extra index you're going to slow down inserts, updates and deletes that much more.
• Simplicity - for small tables of data that almost never changes they are unnecessary. For example, if you need a list of countries you can use the ISO list of countries. It includes meaningful abbreviations. This is better than a surrogate key because it's both small and useful.

In general, surrogate keys are useful, just keep in mind the cons and don't hesitate to use natural keys when appropriate.

You need primary keys on these tables. You just don't know it yet.

If you use small keys like this for Clustered Indexes, then there's quite significant advantages.

Like:

Inserts will always go at the end of pages.

Non-Clustered Indexes (which need a reference to the CIX key(s)) won't have long row addresses to consider.

And more... Kimberly Tripp's stuff is the best resource for this. Google her...

Also - if you have nothing else ensuring uniqueness, you have a hook into each row that you wouldn't otherwise have. You should still put unique indexes on fields that should be unique, and use FKs onto appropriate fields.

But... please consider the overhead of creating such things on existing tables. It could be quite scary. You can put unique indexes on tables without needing to create extra fields. Those unique indexes can then be used for FKs.