I remember asking the same question 25 years ago when I was using a product call storefront. That software was notorious for creating tremendous gaps in the order numbers. This was a major concern for us from an accounting perspective that I will discuss below.
The Internet works on a stateless protocol, it attaches to its source, and then it disconnects. It only reattaches to the source if it needs additional information, or you are sending information back to the source. Each additional connection is treated as a new connection from the server's perspective, what holds everything together is usually a session ID.
Although I have not reviewed the source code, I'm pretty certain that the order number is created during the first commit. (When the table is in insert mode, not edit mode). This makes sense because after the first commit, triggers are generated and the order ID needs to be assigned at this point. You don't want emails going out without order IDs.
It is possible while multiple commits are occurring, that a rollback is instigated, or the initial commit to the database fails, and it tries again to commit to the database. It is possible at this point that a gap will appear.
So many things that can occur when dealing with a stateless protocol, where hiccups can indeed occur. The only true sequential numerator that you're ever going to find is the auto ID, or the increment integer field that is actually controlled by the SQL Server itself. This is significant from an accounting standpoint.
If you're ever audited by either federal or state, they are looking for order numbers that are sequential with no gaps. If they detect fraud, they will assign value to the missing orders and hold you responsible.
Let's face it, government agencies are not up-to-date on newer technology. The Lifesaver here is the auto ID or the increment integer field that is assigned by the server itself. This will always show, at lease it should, a sequence numerical sequence without gaps. But this will only hold true, if you do not delete orders. If you do, and they detect fraud, well, you're just screwed.
This is why we do not delete orders, we cancel them. So in the event of an audit, I can always show a numerical sequence without interruption.
Is there a way for the order ID to be sequential without interruption? The answer would be yes, but it would involve committing the row first, then putting the row in edit mode, grabbing the auto ID or the integer field control by the database and adding a custom value. But of course if things are triggered right after an initial commitment, you're gonna run into problems.
I understand where you're coming from, I like everything in order, and from an accounting perspective, it's a lot easier to keep track of. I wonder if it would be a good idea, if you would introduce the auto ID somewhere in your order print out, let's say as a tracking ID.
Anyway, when working with a stateless protocol, so many factors are involved that these types of occurrences will happen.
I hope this helps.
-Ray
