How can I generate strictly increasing numbers

Question

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)

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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?