Accountants care about receipt and invoice numbers more than developers expect. If they see S-10481, S-10482, S-10484, the first question is: who deleted S-10483?

The usual tools don't help:

IDENTITY and SEQUENCE hand out a value and never take it back. When the transaction rolls back (card declined, stock check failed, a timeout), the number is gone and you have a gap.

MAX(Number) + 1 gives two simultaneous sales the same number.

This is how Tilly POS does it. It's an excerpt from my ebook, Building a Production POS in ASP.NET Core .

A counter row, updated inside the sale's transaction

One small table holds the next value per business and per sequence name:

CREATE TABLE [ common ].[ NumberSequences ] ( [ TenantId ] uniqueidentifier NOT NULL , [ Name ] varchar ( 30 ) NOT NULL , [ NextValue ] bigint NOT NULL , CONSTRAINT [ PK_NumberSequences ] PRIMARY KEY ([ TenantId ], [ Name ]) );

Enter fullscreen mode

Exit fullscreen mode

Reserving a number is one locked UPDATE that returns the old value:

public…