2

I am in the situation where multiple user inserting values from application to database via web service, have using stored procedure for validate and insert records.

Requirement is create unique number for each entries but strictly in SEQUENCE only. I added Identity column but its missed some of the number in between e.g. 25,26,27,29,34...

Our requirement is strictly generate next number only like we use for Invoice Number/ Order Number/ Receipt Number etc. 1,2,3,4,5...

I checked below link about Sequence Number but not sure if its surely resolve my issue. Can someone please assist in this.

Sequence Numbers

bd coder
  • 51
  • 1
  • 7
  • 1
    Having a counter table is a good way to do this. See the second part in [this answer](https://stackoverflow.com/a/35418227/243373). The alternative described by Gordon's answer is not practical in some scenarios. Sometimes the ID needs to be assigned as soon as the row (representing an object, like an invoice) is created. – TT. Jun 10 '17 at 16:24
  • Hi TT yes in my case the ID has to be assigned immediately and return back to the app for further process. If I will use Counter table to store last value what are the chances that in future any two user get same number. Will it be possible at all. – bd coder Jun 12 '17 at 09:03
  • If you use the second part of the answer I gave you, the chance of two users getting the same number is nil (i.e. no chance). This presumes of course that you do everything in one transaction, so get the next number in the sequence and assign the ID to the invoice in one (SQL) transaction. Note the BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION statements in the second part of the answer. These make sure that nothing can go wrong. Either the transaction succeeds and you get the next number, or the transaction fails somehow, and no new number was generated and assigned. – TT. Jun 12 '17 at 11:01
  • Also, within the transaction the counter table is locked so it cannot be modified by other transactions (i.e. other users). If two users would try make a new ID, one will be able to proceed and generate and assign an ID, the other will block until the other user finishes the transaction. This ensures that no duplicate IDs are generated, and that there are no holes in the numbers generated. – TT. Jun 12 '17 at 11:07
  • Hi TT I have tested your second code with 100000 insert in 3 concurrent request and all 300000 rows generated unique increase number without any gap. So it will resolved my problem. Thanks TT. – bd coder Jun 14 '17 at 09:32
  • Hi bd coder. I am glad it solved your problem. Could you do me a favour and upvote the answer I linked to? Thanks! – TT. Jun 14 '17 at 09:53
  • Possible duplicate of [How to get the next number in a sequence](https://stackoverflow.com/questions/35261411/how-to-get-the-next-number-in-a-sequence) – TT. Jun 14 '17 at 10:06

1 Answers1

1

If you absolutely, positively cannot have gaps, then you need to use a trigger and your own logic. This puts a lot of overhead into inserts, but it is the only guarantee.

Basically, the parts of a database that protect the data get in the way of doing what you want. If a transaction uses a sequence number (or identity) and it is later rolled back, then what happens to the generated number? Well, that is one way that gaps appear.

Of course, you will have to figure out what to do in that case. I would just go for an identity column and work on educating users that gaps are possible. After all, if you don't want gaps on output, then row_number() is available to re-assign numbers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon thanks for your help but if sequence number will not be rolled back then is it ok to use Sequence Number in this scenario? – bd coder Jun 12 '17 at 09:12