Reasons not to use an auto-incrementing number for a primary key

Question

I'm currently working on someone else's database where the primary keys are generated via a lookup table which contains a list of table names and the last primary key used. A stored procedure increments this value and checks it is unique before returning it to the calling 'insert' SP.

What are the benefits for using a method like this (or just generating a GUID) instead of just using the Identity/Auto-number?

I'm not talking about primary keys that actually 'mean' something like ISBNs or product codes, just the unique identifiers.

Thanks.

Answer 1

An auto generated ID can cause problems in situations where you are using replication (as I'm sure the techniques you've found can!). In these cases, I generally opt for a GUID.

If you are not likely to use replication, then an auto-incrementing PK will most likely work just fine.