Number sequence: an alternative to no. series?

Hello reader, in this article I want to talk to you about a second possibility to create records with number sequence without going through the “No. Series” table.

In the past, when you wanted to insert a sequential number, the “No. Series” table was used by setting a numerator code and then from the code it was necessary to manage the sequential creation through the functions made available by the codeunit 396 “NoSeriesManagement” for example using the GetNextNo function.

From the version of Microsoft Business Central 2019 wave 2, it is possible to use number sequence. They are based on SQL sequence, which means they are not associated with any table.

Number sequence is generated in ascending order at predefined intervals. There is no mandatory sequentiality mechanism on the numbers; this means that, if for some reason, after having detached a new number from the number sequence, an error is issued by the system, when the generation of the sequence is called at a later time, the number will not be the one obtained at the time of the error, but it will be the next number, causing a “hole” in the numbering.

Let’s see, better, with an example of what it is. Suppose we have a table with a field of type Integer “Number Series Id” which is the ID of the number sequence and a description of type Text[100].

Then we create a list type page for the table and add two actions. One that involves the insertion of 5 records and one that involves the insertion of 5 records, but with an error to test its behavior.

Let’s analyze the first action, it calls the function InsertFiveRecord of the codeunit ‘Number Series Test DZ’. We see that to create the number series I created the function CreateNumberSequence inside which has the creation of the number series through the command:

NumberSequence.Insert(‘TestDZ’, 0, 1, true)

Where ‘TestDZ’ is the name of the number series; 0 represents the starting number of the sequence; 1 is the step of the sequence and the last parameter indicates whether the sequence is for company or not.

If we click on the action, we will see that the system has created 5 records starting from ID = 0 as indicated in the command for the creation of the numerical sequence.

Now, let’s analyze the function that generates an error: simply at the third loop of the FOR cycle I issued an error with rollback.

If we then try to re-insert the 5 records, we see that the sequence starts again from 8 because it was used for indixes 5-6-7, the error caused a rollback of the application and the subsequent use of the number sequence made it start again from index 8.

This demonstrates that number sequence are useful for all those cases where you want to increase performance because there are no lock tables as there might be using classic serial numbers; however, they have the disadvantage that if the numbering must be consequential (for example for posted sales invoices) this technique cannot be used, but you must always go through the classic serial numbers.

The functions of NumberSequence are:

Method nameDescription
[Insert(String, BigInteger][, BigInteger], [Boolean])Creates a number sequence in the database, with the given parameters.
Exists(String[, Boolean])Checks whether a specific number sequence exists.
Delete(String[, Boolean])Deletes a specific number sequence.
Next(String[, Boolean])Retrieves the next value from the number sequence.
Current(String[, Boolean])Gets the current value from the number sequence, without doing any increment. The value is retrieved out of transaction. The value will not be returned on transaction rollback.

I hope this article has given you additional insights on the use of number sequences; moreover, if you want to delve into what a SQL number sequence is, you can find the appropriate documentation at this link.

Leave a Reply