AutoIncrement fields on databases without autoincrement field

Question

In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I really was needing implement a "soft autoincrement". It's because my system is DB independent, so I create the autoinc value programatically in c#.

I would like about solutions for autoincrement fields on databases without autoincrement, what the solution that your use and why? There is some Sql Ansi statement about this? and generating directly from my c#, is a better solution?

PS: I know that select max(id)+1 from table it's not really concurrent friendly...

Answer 1

The mechanism to generate unique id values must not be subject to transaction isolation. This is required for the database to generate a distinct value for each client, better than the trick of SELECT MAX(id)+1 FROM table, which results in a race condition if two clients try to allocate new id values concurrently.

You can't simulate this operation using standard SQL queries (unless you use table locks or serializable transactions). It has to be a mechanism built into the database engine.

ANSI SQL did not describe an operation to generate unique values for surrogate keys until SQL:2003. Before that, there was no standard for auto-incrementing columns, so nearly every brand of RDBMS provided some proprietary solution. Naturally they vary a lot, and there's no way to use them in a simple, database-independent manner.

• MySQL has the AUTO_INCREMENT column option, or SERIAL pseudo-datatype which is equivalent to BIGINT UNSIGNED AUTO_INCREMENT;
• Microsoft SQL Server has the IDENTITY column option and NEWSEQUENTIALID() which is something between auto-increment and GUID;
• Oracle has a SEQUENCE object;
• PostgreSQL has a SEQUENCE object, or SERIAL pseudo-datatype which implicitly creates a sequence object according to a naming convention;
• InterBase/Firebird has a GENERATOR object which is pretty much like a SEQUENCE in Oracle; Firebird 2.1 supports SEQUENCE too;
• SQLite treats any integer declared as your primary key as implicitly auto-incrementing;
• DB2 UDB has just about everything: SEQUENCE objects, or you can declare columns with the "GEN_ID" option.

All these mechanisms operate outside transaction isolation, ensuring that concurrent clients get unique values. Also in all cases there is a way to query the most recently generated value for your current session. There has to be, so you can use it to insert rows in a child table.