602SQL Documentation Index  

Sequence Description

sequence_description :== SEQUENCE [scheme.]sequence_name [ sequence_parameter  ] 
sequence_parameter :== AS type | START WITH integer_number | RESTART WITH integer_number 
    | INCREMENT BY integer_number 
    | MAXVALUE integer_number | NOMAXVALUE | MINVALUE integer_number | NOMINVALUE 
    | CYCLE | NOCYCLE | CACHE integer_number | NOCACHE | ORDER | NOORDER

The integer_number is a BIGINT type number (64-bit integer - c. +/-9223372036854775000).

You can specify these items when creating or modifying a sequence:

An integer value containing the actual value is always bound to a sequence. The actual value is the value that is returned as the following sequence number.

If the value type of a sequence is specified, it has to be some of the integer types. 602SQL ignores this type and always uses BIGINT. The AS clause is allowed only for the SQL 2003 compatibility.

The default values of nonspecified parameters are START WITH 0 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20. The nonspecified parameters in the ALTER SEQUENCE statement are preserved from the original sequence definition. The value that should be generated as next is also preserved (if it belongs to the interval between the maximum and minimum value).

The sequence step cannot be zero. If the step is negative, the values are descending.

Maximum must be greater than minimum and the sequence initial value must be in between. If the sequence step is positive, the default value for minimum will be 1, the default value for maximum will be the highest possible integer value and the initial value will equal to the minimum. If the sequence step is negative, then the default minimum value will be the lowest possible integer number, the default maximum value will be 1 and the initial value will equal to the maximum.

If the cyclic number generation is enabled using the CYCLE parameter, then the sequence will automatically generate its (set of default) minimum (or maximum for descending sequences) value as the next value when it reaches its maximum (or minimum).

Buffer utilization significantly fastens the value generation. The whole cache is filled when the first need to allocate a value arises. If some cache values are not used, they'll be returned into the sequence when the server is shutting down. If the server is not correctly terminated because of a system failure, the cache values will be lost.

The ORDER and NOORDER parameters are ignored.

The actual value of a sequence is preserved when exporting an application with data. A sequence is reset when exporting application without data (it's set to the initial value).

Usage Example:

CREATE SEQUENCE SEQ10
  START WITH 0
  INCREMENT BY 10
  NOMAXVALUE
  NOMINVALUE
  CACHE 5
  NOCYCLE

Variances from the SQL Standard

See