Gap-free invoice numbers in SQL Server (and why IDENTITY can't give you them)

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])
);

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

public async Task<long> ReserveAsync(Guid tenantId, string name, int count = 1, CancellationToken ct = default)
{
    for (var attempt = 0; ; attempt++)
    {
        var reserved = await db.Database.SqlQuery<long>($"""
            UPDATE [common].[NumberSequences] WITH (UPDLOCK, HOLDLOCK)
            SET [NextValue] = [NextValue] + {count}
            OUTPUT deleted.[NextValue] AS [Value]
            WHERE [TenantId] = {tenantId} AND [Name] = {name}
            """).ToListAsync(ct);
        if (reserved.Count == 1) return reserved[0];

        try
        {
            await db.Database.ExecuteSqlAsync($"""
                INSERT INTO [common].[NumberSequences] ([TenantId], [Name], [NextValue]) VALUES ({tenantId}, {name}, {1 + count})
                """, ct);
            return 1;
        }
        catch (SqlException ex) when (ex.Number is 2627 or 2601 && attempt == 0)
        {
            // Another request created the row first; loop round and take the UPDATE path.
        }
    }
}

(The interpolated values are parameters, not string concatenation — SqlQuery and ExecuteSqlAsync take a FormattableString.)

Why it has no gaps

The UPDATE runs inside the same transaction as the sale:

await using var tx = await db.Database.BeginTransactionAsync(ct);

var number = $"S-{await sequences.ReserveAsync(tenantId, SequenceNames.Sale, 1, ct) + 10000}";
db.Sales.Add(sale);
// ... payments, stock movements, audit entry

await db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);

• If the sale fails, the transaction rolls back — and so does the counter. The next sale gets the same number the failed one would have had.
• UPDLOCK makes a second sale for the same business wait at the counter row until the first commits or rolls back, so two sales can never read the same value.
• HOLDLOCK keeps the lock to the end of the transaction, so the "no row yet" case can't race either; if two first-ever sales both try to INSERT, one hits the primary key and simply goes round to the UPDATE.

The cost, and why it's fine

Sales for one business now number themselves one at a time. The lock is held only from the reservation to the commit — a few milliseconds — and different businesses never wait for each other, because the key includes the tenant.

For a point of sale, invoicing or anything an accountant reads, that's exactly the right trade: correctness at the cost of a tiny, local queue.

More like this

The ebook covers the rest of the checkout the same way: idempotency keys against double charges, a conditional UPDATE that stops two tills selling the last unit, stock as a ledger, multi-tenancy, refresh-token rotation and an append-only audit log — 23 pages with the real code. Building a Production POS in ASP.NET Core