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

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.

Answer 2

good article:

http://www.simple-talk.com/sql/t-sql-programming/identity-columns/

Answer 3

You cannot (normally) specify values when inserting into identity columns, so for example if the column "id" was specified as an identify the following SQL would fail:

INSERT INTO MyTable (id, name) VALUES (1, 'Smith')

In order to perform this sort of insert you need to have IDENTITY_INSERT on for that table - this is not intended to be on normally and can only be on for a maximum of 1 tables in the database at any point in time.

Answer 4

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 5

I'm not sure I understand enough about your context, but I interpret your question to be:

"If I need the database to create a unique column (for whatever reason), when shouldn't it be a monotonically increasing integer (identity) column?"

In those cases, there's no reason to use anything other than the facility provided by the DBMS for the purpose; in your case (SQL Server?) that's an identity.

Except:

If you'll ever need to merge the table with data from another source, use a GUID, which will prevent duplicate keys from colliding.

Answer 6

If you need to merge databases it's a lot easier if you don't have to regenerate keys.

Answer 7

This question has some good answers:

• Is using MS SQL Identity good practice?

Answer 8

If I need a surrogate, I would either use an IDENTITY column or a GUID column depending on the need for global uniqueness.

If there is a natural primary key, or the primary key is defined as a unique combination of other foreign keys, then I typically do not have an IDENTITY, nor do I use it as the primary key.

There is an exception, which is snapshot configuration tables which I am tracking with an audit trigger. In this case, there is usually a logical "primary key" (usually date of the snapshot and natural key of the row - like a cost center or gl account number for which the row is a configuration record), but instead of using the natural "primary key" as the primary key, I add an IDENTITY and make that the primary key and make a unique index or constraint on the date and natural key. Although theoretically the date and natural key shouldn't change, in these tables, if a user does that instead of adding a new row and deleting the old row, I want the audit (which reflects a change to a row identified by its primary key) to really reflect a change in the row - not the disappearance of a key and the appearance of a new one.

Answer 9

One case of not wanting an identity field would be in a one to one relationship. The secondary table would have as its primary key the same value as the primary table. The only reason to have an identity field in that situation would seem to be to satisfy an ORM.

Answer 10

I recently implemented a Suffix Trie in C# that could index novels, and then allow searches to be done extremely fast, linear to the size of the search string. Part of the requirements (this was a homework assignment) was to use offline storage, so I used MS SQL, and needed a structure to represent a Node in a table.

I ended up with the following structure : NodeID Character ParentID, etc, where the NodeID was a primary key.

I didn't want this to be done as an autoincrementing identity for two main reasons.

1. How do I get the value of a NodeID after I add it to the database/data table?
2. I wanted more control when it came to generating my own IDs.