Best way to get totally sequential int values in SQL Server

I have a business requirement that the InvoiceNumber field in my Invoices table be totally seque开发者_Python百科ntial - 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?

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.

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.

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.