CREATE SEQUENCE

This topic describes how to create different types of sequences.

New sequences

Syntax

CREATE [NEW] SEQUENCE <name>
[ START WITH <numeric value> ]
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameters

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. Examples

Create a new sequence in which the start value is 1000.

CREATE NEW SEQUENCE newseq START WITH 1000;

Create a new sequence in which the start value of 1, the step size is 2, the maximum value is 100, the start value cannot be used again.

CREATE NEW SEQUENCE newseq2 START WITH 1 INCREMENT BY 2 MAXVALUE 100 CYCLE;

Group sequences

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 {#concept-2068722-entry-fp4-adx-5au}

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

Create a group sequence that contains only one unit.

CREATE GROUP SEQUENCE groupseq;

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 sequences

Syntax

CREATE TIME SEQUENCE <name>

Important The column that is used to store the values of a time sequence must be of the BIGINT data type. Example

Create a time sequence.

CREATE TIME SEQUENCE seq3;