VB.Net: Manage ID in tables in a multi-users applicaton

Question

Hi everyone,

I look at Wide World Importers and the way Microsoft has setup the sequences to generate ID, I keep that principle and I have created a sequence for OrderID named it Sequences.OrderID has the WWI sample database is showing us, I plan to use that sequence in my table Sales.Orders.

My application will be use in a multi-users environment so in my ordering module, I am saving the data and using the sequence only when the user has finish to enter the products in the order. By doing so I think that the chances to have a gap in the OrderID is minimal unless a rollback is possible.

Here is my question, with all the sophisticated possibilities on SQL and Visual Studio, how can it be possible to not have a gap

in the orders table when an app is use by several users at the same time? Is is possible or not? and if it is, can you direct me

in the right direction.

Note: I already have created a new query in the OrdersTableAdapter:

SELECT NEXT VALUE FOR Sequences.OrderID
Save as:    ScalarOrderIDQuery
Me.OrderIDTB.Text = Me.OrdersTableAdapter.ScalarOrderIDQuery()

I am using the last phrase in these 3 lines to obtain the next ID and it works for now, but I did not test in a multi-user environment.

Thanks

Being from Quebec, please forgive the mistakes I might do :)

Answer 1

If you don't want gaps, you should not use sequences or IDENTITY. They are designed to be able to produce gaps. If you want a contiguous series of ids, you need to roll your own:

BEGIN TRANSACTION

SELECT @id = isnull(MAX(id), 0) FROM tbl WITH (UPDLOCK)

INSERT tbl(id, col1, col2, ...
   SELECT @id, @par1, @par2, ---

...

But this also means that if there are parallel inserts they will be serialised. Not a big deal if inserts are occasional, but if there can be several per second, this pattern is not good. That's when you use a sequence or IDENTITY. And once you have decided to do that, it's kind of futile to care about gaps.

Besides gaps due to rollbacks, you can also get rollbacks due to that the sequence value is cached in memory and it is only updated on disk occasionally. This means that if the database is shut down uncleanly, the value on disk will not be updated to a lower value, but there will be a gap next time the database is active. The same is true for IDENTITY.