Continuous Autonumber

I have an SQL Server table开发者_运维知识库 where I store invoices lets call it invoice. I need to implement a continuous autonumber feauture for invoices (not the autoidentity which is not continuous). Moreover I should take care of concurrency issues for example user A and user B invoice the same time but the two invoices should not have the same number (obviously).

What would be an ideal implementation method in T-SQL?

---

One way we did something similar was to create a table called useID with only one column called [ID]. We use an Integer data type for it. This table also only has one row. More on that in a bit.

Now, each time we need to log an event we SELECT from useID and run our transaction with this [ID] value being used for tracking purposes. Right after we have SELECTed the [ID] we increment the value in useID by 1 (or whatever we need for the system in question). In this way we maintain unique and contiguous [ID] values. We can delete from the destination of the [ID] value without affecting the order of the new [ID] values. Performance on this is very good as we run ~10million transactions a night using this and we do reset the starting value every 3 months or so since we do not keep items 'live' that long.

---

An IDENTITY column.

If you need an invoice number that is alphanumeric I suggest you update your question with the required format.

There will only be gaps if you delete records, experience an error during an INSERT, rollback a transaction that contain an INSERT(s) into the table, or the seed is updated by a relevant dbcc command.

If you really have to reuse gaps (and I certainly wouldn't do so for things like invoices, for instance, in your example invoice #32 would have a later date then invoice #190 ...): then you could, in a serializable transaction, find lowest free value, set identity insert on, insert a row with that Id value, and then set identity insert off and commit the transaction.

Something like this (untested):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN
SET IDENTITY_INSERT dbo.myTable ON

DECLARE @minId int = -1

;WITH cterows(Id, rownum)
AS
(
   SELECT Id, row_number() OVER(ORDER BY Id ASC) AS rownum 
)
SELECT @minId = MIN(rownum) FROM cterows
WHERE Id <> rownum

IF (@minId IS NOT NULL AND @minId <> -1) 
   BEGIN
     -- found a gap
     -- Insert at @minId
   END
ELSE
   BEGIN
     -- No gap, INSERT as normal
   END


SET IDENTITY_INSERT dbo.myTable OFF;
COMMIT

---

If it isn't necessary these numbers to be continues, you can create random number,but if it should be continues you can make that column IDENTITY colunm

check this post to create your random number

---

You can make a SQL Varchar column for alphanumeric invoice number, Here you need to generate unique invoice no or can use IDENTITY column.