I need to put numbers in sequential order (1, 2, 3, 4...) in a table column. These are invoice numbers, they need to be consecutives, no gaps, no repeated. Is a mandatory requirement, in fact is a fiscal requirement, so I can't skip it.
My current aproach is to use a second "Numbers" table with 2 columns (Id, LastNumber) with one record on it, Id = 1. This is what I'm doing now: 1. SELECT (LastNumber + 1) as Number from Numbers with (xlock, rowlock) where Id = 1 2. assign the number, do other inserts and updates to other tables. 3. UPDATE Numbers set LastNumber = @Number where Id = 1 --@Number is the number retrieved in step 1 4. End of Transaction
I'm trying to use locking, and don't know if I am correct, what is the most efficient solution to do this? As I stated above, is a mandatory requeriment, the number must be consecutives, no gaps, no repeated. The table is used in an app from several clients in a network. I use Sql Server 2008 R2.
I have found this similar question: Sequential Invoice Numbers SQL server
the solution given there is with some T-SQL code that get the max number in the table, but what happens if 2 clients called this code at the same time? 2 repetead numbers will be generated?
Thanks
You'll want a transaction. By doing the update first, you're effectively locking the row from other shared locks until your transaction is completed, then returning the new number.
BEGIN TRAN
UPDATE Numbers SET Number = Number + 1 WHERE Id = 12
SELECT Number FROM Numbers WHERE Id = 12
COMMIT