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.

Syntax

The CREATE SEQUENCE statement uses the following syntax:

CREATE SEQUENCE [schema_name]sequence_name  
    [INCREMENT BY ],
    [START WITH value],
    [MAXVALUE value| NOMAXVALUE,]    [MINVALUE value| NOMINVALUE],
    [CYCLE | NOCYCLE]    [MULTI_TENANT]    ;

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,
     INCREMENT BY 1,
     NOCYCLE
;

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,
     INCREMENT BY 1,
     NOCYCLE
     MULTI_TENANT
     ;

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 SQL Reference.