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?
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
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.
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.
