Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Definition Language : Using Data Definition Language statements : Working with sequences

Working with sequences

Sequences are database objects that automatically generate numbers in a sequential order. Usually, sequences are used to provide a table with a series of unique numbers such as primary key values. 32-bit sequences enable you to generate unique sets of INTEGER values and 64-bit sequences enable you to generate unique sets of BIGINT values.
A sequence defined as a terminating sequence provides a guarantee of order and uniqueness. A sequence defined to cycle at a limit provides applications with a control value that can be combined with another value to provide a unique value. Sequences provide you with an alternative to a control table for the generation of values. Sequences offer less contention in reading and generating values and therefore perform better than control tables.
To create a multi-tenant sequence, use the MULTI_TENANT attribute after defining all the other sequence attributes in the CREATE SEQUENCE syntax.
The CREATE SEQUENCE statement uses the following syntax:
CREATE SEQUENCE [schema_name]sequence_name  
    [START WITH value],
Examples: CREATE SEQUENCE statement
In the following example, a sequence is used to generate unique customer numbers when a new row is inserted into the table pub.customer.

CREATE SEQUENCE pub.customer_sequence
     START WITH 100,
Example: Creating a multi-tenant sequence
The following example shows how to define a multi-tenant sequence:

CREATE SEQUENCE pub.mtcustomer_sequence
     START WITH 100,
Sequences can be:
*Sequential values within any range of an OpenEdge INTEGER or BIGINT datatype
*Incremented or decremented
*Configured with an initial value
*Configured to terminate at a specific limit
*Configured to cycle at a specific limit
A sequence number is generated independently of the transaction which is committing or rolling back. It is possible that individual sequence numbers will appear to be skipped because they were generated and used in a transaction that ultimately rolled back. Sequence numbers are generated independently of tables so they can be used for more than one table.
For more on the CREATE SEQUENCE statement, see OpenEdge Data Management: SQL Reference.
* Enabling 64-bit sequences
* Using CURRVAL and NEXTVAL in a statement
* Working with existing 32-bit sequences