I need to assign a number to a document - an RFQ, quote, order, job, etc. The table that will hold this number has a primary key field of type integer, flagged as identity so it will increment automatically. Should I use the value of this field as my serial number, or is it better to have a different field SN of type integer and assign a new record the value max(SN) + 1? Or is there a better way than either of these?
I have often used something like this. Assume that the MyIDValues table has only one row, so IDColumn always holds the most-recently-assigned ID value:
DECLARE @NewID Int
UPDATE MyDB.dbo.MyIDValues SET @NewID = IDColumn = IDColumn + 1
SELECT @NewID
It simultaneously (i.e. as an atomic statement) updates the table to set the incremented value and returns that value.
In my opinion, this is better than taking the current top value because it always avoids collisions (i.e. if you have software that incorrectly does take the top value) by incrementing first and then returning that incremented value.