How can I generate strictly increasing numbers

Chris Sijtsma 141 Reputation points

Dear colleagues,

I use SQL Server 2019, Standard Edition.

Our table with journal entries has a strictly increasing PK field. Nothing weird there.
With strictly increasing I mean that even when a rollback happens, the next insert will not cause a gap.
We have a table with one record containing the last used number. Every transaction posting a journal entry does the following.

  • Start a transaction
  • Lock the table with the last used number (always the first action to prevent deadlocks on this table)
  • Get the number, increment it and post the new number
  • Do the rest
  • Commit or Rollback (when a rollback is issued, also the increment is rolled back)

This works, but when a long running transaction creates journal entries, this causes awful performance degradation (for obvious reasons). I cannot use an identity column because the journal entries are stored in several tables sharing the same PK. I want to use a sequence to prevent the problems we have now. However, even if the sequence doesn't cache values, we still can have gaps in the numbering in case of a rollback. The accountants do not like this.

My question:
Is there a way to add a new column that I can populate with a strictly increasing number that is assigned after transactions are committed? As far as I know, there is no way I can receive a notification when a transaction is committed. I do not find it a problem if the new column doesn't have the same order as the PK. This might happen in the following scenario.

  • Last used PK = x, last used stricly increasing number = y
  • Transaction A starts and gets PK = x+1
  • Transaction B starts and gets PK = x+2
  • Transaction C starts and gets PK = x+3
  • Transaction C commits and gets PK = x+3, Increasing number = y+1
  • Transaction B rolls back
  • Transaction A commits and gets PK = x+1, Increasing number = y+2
    (gap between x+1 and x+3, increasing number in different order)
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,896 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,716 Reputation points

    You are apparently talking about wanting a sequence based on the order transactions are committed. There are several reasons why identity/sequences skip number, mostly for performance and blocking with large numbers of transactions.

    There is nothing built-in which would run after commit. The simplest answer to your question is to create a trigger AFTER INSERT which uses a sequence or max(seq) and inserts the value. However, depending on where that runs, it might also skip numbers and is going to be prone to blocking if you have a large number of transactions.

    You are likely better off creating a job to update the number. But you still don't know the order of the items committed.

    Without knowing more about what exactly you are trying to do, it is hard to give an exact answer.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points

    Presumably, you need an index on this journal-number value anyway, so just slap that index on the table, and make it unfiltered. The NULL values be in one end of the index. Updating with row_number + the current max certainly is the most straightforward solution.

    But updating an index key comes with an extra cost, and possibly there can be conflicts with other actions in the system.

    1 person found this answer helpful.
    0 comments No comments

  2. Chris Sijtsma 141 Reputation points

    Some more info, I do not mind if the 'strictly increasing' number is assigned later (say with a scheduled job) and records will not have a number for a short period. I thought about adding a filtered index on my journal table on the strictly increasing number column for the value NULL and also one for the value NOT NULL. In that way I can quickly retreive the last used number and assign the new records a number with ROW_NUMBER(). Is this the way to go, or do you have other ideas.

    0 comments No comments

  3. Chris Sijtsma 141 Reputation points

    Thank you both. And what about an insert trigger using a sequence while I specify that the trigger has to be the last one to execute?