Non-blocking number series – What is behind?
In Wave 2 of 2019 Microsoft has added new functionality to Number Series. It is called Lock-free number series or Non-blocking number series. It was introduced to optimize performance. The feature description you can find below
Users can choose to mark number series to allow gaps in the series. These number series will be non-blocking, which will boost performance.
Selected non-financial number series in the application will be made non-blocking.
How to use it?
In the Number Series Lines, you can mark the field Allow Gaps in Nos.
You can use it for all areas which do not need, from a functional or legal perspective, to be a consecutive. For example Customer No., Vendor No. etc.
What does it mean? If the series would not have the checkmark, the last used number will be reversed, if the transaction will be stopped.
If you would switch on the functionality then, even if the transaction will be reversed, the number will be already used.
You can test it with the below code. Then set the number of series with Allow Gaps in Nos. and when creating a customer stop the transaction after assigning the number during insert. Next time when you will create the customer the number which you will use is already with a gap.
How does it work? The number is not stored in any table. When the functionality is on for number series line, in the background the SQL Sequence is created.
How the SQL Sequence works you can read in a very good article here: http://www.sqlservertutorial.net/sql-server-basics/sql-server-sequence/
There you can also find when to use the sequences. In short:
- The application requires a number before inserting values into the table.
- The application requires sharing a sequence of numbers across multiple tables.
- The application requires to restart the number when a specified value is reached.
- The application requires multiple numbers to be assigned at the same time.
Your Number Sequence
If you need you also can create a number sequence from the code directly. For that, there is a new Data Type – NumberSequence. It has a few methods: Insert, Exist, Delete. Also two more: Next – to get the next number in the NumberSequence and Current – to get the current number.
When inserting you can specify the name, starting number and increment by.
The effect of the code you can find below. As you can see the name of the sequence is the same as assigned. It is different than using Number Series functionality – there the name is created as GUID when marking field Allow Gaps in Nos. and it is stored in Number Series Line Table.
Since the name of the number sequence must be unique always check if it exists before inserting (at this moment the “Something Went Wrong” screen is shown when try to double insert the same sequence). To be sure it is unique I think a good idea is to add your affix to the number sequence.
By default, the number sequence is created per company. However, there is possible to set that number sequence is common across companies. There is no method to reset the number sequence however you can delete it and create one more time.
More information you can find here: