When having an identity column is not a good idea?

Question

In tables where you need only 1 column as the key, and values in that column can be integers, when you shouldn't use an identity field?

To the contrary, in the same table and column, when would you generate manually its values and you wouldn't use an autogenerated value for each record?

I guess that it would be the case when there are lots of inserts and deletes to the table. Am I right? What other situations could be?

Answer 1

If you already settled on the surrogate side of the Great Primary Key Debacle then I can't find a single reason not use use identity keys. The usual alternatives are guids (they have many disadvatages, primarily from size and randomness) and application layer generated keys. But creating a surrogate key in the application layer is a little bit harder than it seems and also does not cover non-application related data access (ie. batch loads, imports, other apps etc). The one special case is distributed applications when guids and even sequential guids may offer a better alternative to site id + identity keys..

Answer 2

I suppose if you are creating a many-to-many linking table, where both fields are foreign keys, you don't need an identity field.

Nowadays I imagine that most ORMs expect there to be an identity field in every table. In general, it is a good practice to provide one.