Number sequences in Business Central

APPLIES TO: Business Central 2019 release wave 2 and later

In AL, you can create and manage number sequences that generate numeric identifiers for data and records. Business Central number sequences are built on SQL Server sequences, which means that they are not associated with any tables. Instead, the application code references the number sequence object and coordinates the values across records.

The numbers in a number sequence are generated in ascending order at a defined interval. Numbers are used sequentially, but numbers can be skipped. This means that numbers used on records in tables can have gaps. These gaps, for example, can occur when transactions are rolled back or numbers are allocated but not used.

Number sequences and number series in the application

Unlike number sequences, number series are specially designed for scenarios where you have to use a continuous numbering system on transactional records. This typically includes documents such as purchase orders, sales orders, and invoices. The drawback of using continuous numbers is that when a number is requested by a transaction, the No Series Line table in the database is locked until the transaction completes. This can potentially block other users from being able to work.

For more information about number series, see Create Number Series in the Business Central application help.

Using non-blocking number series for improved performance

Number series also include an option called Allow Gaps in Nos.. This option actually uses number sequences in the underlying code, and allows you to implement non-continuous, non-blocking numbering. So, if there are no regulatory requirements for using continuous numbering, you can improve performance by allowing gaps and using a number sequence instead. Customer cards, sales quotes, and warehouse activities are examples of entities that typically don't require continuous numbering.

Creating and managing number sequences in AL

To create and manage number sequences, you use the NumberSequence data type and the following methods.

Method name	Description
[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.

Examples (creating and deleting NumberSequence objects)

The following AL examples show how you can create and delete NumberSequence objects (and the corresponding sequence objects in SQL).

// Creates a NumberSequence object that starts with the value '0' and increments by '1'​
NumberSequence.Insert('DefaultSequence');

// Creates a NumberSequence object that starts with the value '10' and increments by '1'​
NumberSequence.Insert('StartsWithTenSequence', 10);

​// Creates a NumberSequence object that starts with the value '0' and increments by '10'​
NumberSequence.Insert('StartsWithZeroIncrementTenSequence', 0, 10); 

​// Creates a NumberSequence object that starts with the value '0', increments by '1', and is company-specific​
NumberSequence.Insert('MyCompanySequence', 0, 1, true); ​
​
// Verifies whether a specific NumberSequence object exists, and if so, deletes it
if NumberSequence.Exists('MySequence', true) then
    NumberSequence.Delete('MySequence',true);​​

Examples (using NumberSequence objects)

The following AL examples show how you can use NumberSequence objects in your AL code to generate numbers.

// Gets and returns the current value in a NumberSequence object
number := NumberSequence.Current('MySequence',true);​

// Gets and returns the next value in a NumberSequence object
number := NumberSequence.Next('MySequence',true); ​

See also

Number sequence data type
SQL Server Sequences