This topic describes how to create different types of sequences.
New sequences
SyntaxCREATE [NEW] SEQUENCE <name>
[ START WITH <numeric value> ]
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]| Parameter | Description |
|---|---|
| START WITH | The start value for the new sequence. If you do not configure this parameter, the default start value is used. Default value: 1. |
| INCREMENT BY | The increment between two adjacent sequence values, also called the interval value or step size. If you do not configure this parameter, the default start value is used. Default value: 1. |
| MAXVALUE | The maximum value for the new sequence. The value must be a positive integer. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type. |
| CYCLE or NOCYCLE | You can select only one of the 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.
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
SyntaxCREATE [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.
| 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.
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
SyntaxCREATE TIME SEQUENCE <name>Important The column that is used to store the values of a time sequence must be of the BIGINT data type.
Create a time sequence.
CREATE TIME SEQUENCE seq3;