The way to avoid overkill is to accept two conditions: The container of the Current Value need not be locked throughout a transaction, and need not be written to disk at the end of a transaction (or conversely, need not be unwritten if the transaction ends with ROLLBACK). Besides, updating a user table is clearly overkill for something as elementary as incrementing an integer. The technique is more flexible and the isolation level can be lower, but there's still a bottleneck. This code maintains a one-row table that always has the latest Current Value.

INSERT INTO Table1 VALUES (Current_Value. SELECT Current_Value INTO Current_Value FROM Key_table UPDATE Key_table SET Current_Value = Current_Value + 1 So the code must either be in a SERIALIZABLE transaction, or Table1 must be locked. (In a SQL monotonic sequence, the values always increase or always decrease.) The code has two flaws: A SELECT followed by an INSERT in the same area can cause deadlocks, and two concurrent users will see the same value if they SELECT at the same time. It guarantees a true monotonic sequence because values always increase. The code adds one to the Current Value, and puts the result into a new row. This code finds the highest value for a column: the 'Current Value' of the sequence. INSERT INTO Table1 VALUES (Current_Value + 1. SELECT MAX(identity_column) INTO Current_Value FROM Table1 The Old Way: SELECT MAX and Key Tablesīefore DB2 version 7.2, Oracle6, and SQL Server 6.0, users coded their own sequence generators with 'SELECT MAX' or with 'Key Tables.' SELECT MAX code looks like this: I'll use a convention for some of the specialized vocabulary: a 'Special Term' will be in quotes the first time it appears and will be capitalized if it's a noun. Let's look at the compromises and efficiencies of sequences in Oracle / DB2 SEQUENCE objects and DB2 / SQL Server IDENTITY columns. That's okay, because the main use of sequences - getting short unique numbers for primary keys - is more efficient if some compromises happen.

My Oxford Concise Dictionary says a sequence is a 'set of things belonging next to each other on some principle of order' or a 'series without gaps.' Yet IBM DB2®, Microsoft SQL Server 2000, and Oracle9i can make sequences with both disorder and gaps.

Db2 sequence number