Skip to main content

Sequences

Introduction to sequences

Sequences are database objects that can be used as a counter. A sequence can be queried to get the next number in the series.

When a sequence is created, it will be generated to its own code file which can be run on the database.

menu Data > Sequences

Overview of the sequences screen Sequences

Settings

Domain

The datatype that will be used for the sequence. Note that this domain must be either of an integer type or a numeric type with precision 0. The lookup automatically filters domains that match this requirement.

Start value

The value that the sequence starts at. Note that once the sequence has been created on the database, changing this value will not have any effect. Changing the start value can be done manually by running the following sql command on the database:

alter sequence [sequence]
restart with [value]

Increment value

The number that the sequence is incremented by when retrieving the next value.

Min. value

The minimum value for the sequence. If nothing is entered, the minimum value will be determined by the domain data type.

Max. value

The maximum value for the sequence. If nothing is entered, the maximum value will be determined by the domain data type.

Cycle

Determines whether the sequence cycles back to the start when the maximum value has been reached. If this option is disabled, the sequence will show an error message when it attempts to retrieve more values after reaching the maximum value.

Cache

This setting can be used to cache a number of values in advance. This allows for improved performance by minimizing disk i/o. Note that if the SQL Server database shuts down improperly for whatever reason (a server crash for example), the values that have been cached will be lost. The sequence will resume counting after the last cached value.

Using sequences

The current implementation of sequences cannot be used as a replacement for identity columns. Sequences can however be used freely in any functionality, for example in defaults or default value expressions for a column.

To select a value from a sequence, use the next value for function.

For example:

select next value for order_row_sequence