This topic describes how to create different types of sequences.

New Sequence

Syntax
CREATE [NEW] SEQUENCE <name>
[ START WITH <numeric value> ]
Parameters
Parameter Description
START WITH The start value of the sequence that you want to create. If you do not configure this parameter, the default start value is used. Default value: 1.
Note If you do not specify the sequence type when you create a sequence in a database that is in AUTO mode, a NEW sequence is created by default. You cannot create a NEW sequence in a database that is in DRDS mode.
Example

You can execute the following statement to create a NEW sequence in which the start value is 1000: 

CREATE NEW SEQUENCE newseq START WITH 1000;

Group Sequence

Syntax
CREATE [GROUP] SEQUENCE <name>
[ START WITH <numeric value> ]
[ UNIT COUNT <numeric value> INDEX <numeric value> ]
Note If you do not specify the sequence type when you create a sequence in a database that is in DRDS mode, a GROUP sequence is created by default. If you want to create a GROUP sequence in a database that is in AUTO mode, you must specify GROUP as the sequence type.
Parameters
Parameter Description
START WITH By default, the start value of a GROUP sequence is determined based on the value of the UNIT COUNT parameter and the value of the INDEX parameter. If you do not configure the UNIT COUNT parameter and INDEX parameter, the default start value 100001 is used.
UNIT COUNT The number of units in the GROUP sequence. Default value: 1.
INDEX The unit index of the unit sequence. Valid values range from 0 to the value that is obtained as the difference of the number of units minus 1. Default value: 0.
Note
  • A GROUP sequence contains nonconsecutive values. The value of the START WITH parameter is specified only for reference. The actual start value of the GROUP sequences may be greater than the value of the START WITH parameter in your business scenario.
  • A GROUP sequence that contains multiple units cannot be converted to a sequence of another type.
  • After a GROUP sequence is created, you cannot change the values of the UNIT COUNT parameter and INDEX parameter.
Examples

You can execute the following statement to create a GROUP sequence that contains only one unit: 

CREATE GROUP SEQUENCE groupseq;

You can execute the following statements to create unit sequences in three instances or databases to generate a global GROUP sequence. The sequence name and the number of units that are specified in the definitions of the unit sequences are the same, and the index of each unit sequence is unique.

  • Create a unit sequence in Instance 1 or Database 1.
    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 0;
  • Create a unit sequence in Instance 2 or Database 2.
    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 1;
  • Create a unit sequence in Instance 3 or Database 3.
    CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 2;

Time-based Sequence

Syntax
CREATE TIME SEQUENCE <name>
Notice The column that is used to store the values of a time-based sequence (TIME) must be of the BIGINT data type.
Example

You can execute the following statement to create a TIME sequence.

CREATE TIME SEQUENCE seq3;