Best way to get totally sequential int values in SQL Server

Question

I have a business requirement that the InvoiceNumber field in my Invoices table be totally sequential - no gaps or the auditors might think our accountants are up to something fishy!

My first thought was to simply use the primary key (identity) but if a transaction is rolled back a gap appears in the sequence.

So my second thought is to use a trigger which, at the point of insert, looks for the highest InvoiceNumber value in the table, adds 1 to it, and uses it as the InvoiceNumber for the new row. Easy to implement.

Are there potential issues with near-simultaneous inserts? For example, might two near simultaneous inserts running the trigger at the same time get the same 'currently highest InvoiceNumber' value and therefore insert rows with the same InvoiceNumber?

Are there other issues I might be missing? Would another approach be better?

Wait for SQL Server 2011, which will have `sequences` - see [Aaron Bertrand's blog post on it](https://sqlblog.org/2010/11/11/sql-server-v-next-denali-using-sequence) for more details. Or change those silly requirements...... just guaranteed sequential should be good enough... — marc_s, Apr 04 '11 at 11:12
@marc_s - unfortunately, according to your link to Bertrand's information, sequences in SQL Server 2011 will not reset on rollback, so you would still have gaps. So this would not be an option for the OP. Handling generation of exposed value (i.e. Invoice number, Account Codes, etc.) should probably be handled outside the DB. — JeremyDWill, Apr 04 '11 at 13:59
@jeremydwill: right you are - which brings me back to my original thought: kill that silly requirement.... — marc_s, Apr 04 '11 at 14:05

score 0 · Answer 1 · answered Apr 04 '11 at 11:07

Create a table which keeps tracks of 'counters'. For your invoices, you can add some record to that table which keeps track of the next integer that must be used.
When creating an invoice, you should use that value, and increase it. When your transaction is rolled back, the update to that counter will be rollbacked as well. (Make sure that you put a lock on that table, to be sure that no other process can use the same value).

This is much more reliable than looking at the highest current counter that is being used in your invoice table.

Martin Smith · Answer 2 · 2011-04-04T11:15:07.317

CREATE TABLE dbo.Sequence(
 val int 
 )

Insert a row with an initial seed. Then to allocate a range of sufficient size for your insert (call it in the same transaction obviously)

CREATE PROC dbo.GetSequence
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequence 
SET @val = val = val + @n;

SET @val = @val - @n + 1;

This will block other concurrent attempts to increment the sequence until the first transaction commits.

score 0 · Answer 3 · answered Apr 04 '11 at 11:08

You may still get gaps if data gets deleted from the table. But if data only goes in and not out, then with proper use of transactions on an external sequence table, it should be possible to do this nicely. Don't use MAX()+1 because it can have timing issues, or you may have to lock more of the table (page/table) than required.

Have a sequential table that has only one single record and column. Retrieve numbers from the table atomically, wrapping the retrieval and usage in a single transaction.

begin tran
declare @next int
update seqn_for_invoice set @next=next=next+1
insert invoice (invoicenumber,...) value (@next, ....)
commit

The UPDATE statement is atomic and cannot be interrupted, and the double assignment make the value of @next atomic. It is equivalent to using an OUTPUT clause in SQL Server 2005+ to return the updated value. If you need a range of numbers in one go, it is easier to use the PRE-update value rather than the POST-update value, i.e.

begin tran
declare @next int
update seqn_for_invoice set @next=next, next=next+3   -- 3 in one go
insert invoice (invoicenumber,...) value (@next, ....)
insert invoice (invoicenumber,...) value (@next+1, ....)
insert invoice (invoicenumber,...) value (@next+2, ....)
commit

Reference for SQL Server UPDATE statement

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

Best way to get totally sequential int values in SQL Server

3 Answers3

Linked