Use the CREATE SEQUENCE statement to create a sequence. A sequence is a database object from which multiple users can generate unique integers.
- To create an ascending sequence that increments to its maximum data type value, omit the MAXVALUE or specify NO MAXVALUE. For descending sequences that decrement to the minimum value of 1, omit the MINVALUE or specify NO MINVALUE.
- To create a sequence that stops at a predefined limit for ascending sequences, specify a value for the MAXVALUE parameter. For descending sequences, specify a value for the MINVALUE. Also, specify NO CYCLE. Any attempt to generate a sequence number after the sequence reaches its limit results in an error.
- To create a sequence that restarts after it reaches its limit, specify CYCLE.
After you create a sequence, you can access its value in SQL statement with the NEXT VALUE FOR statement (which increments the sequence and returns the new value).
The system generates sequence numbers independent of whether the transaction commits or rolls back. If two users concurrently increment the same sequence, the sequence numbers each user acquires can have gaps, because the other user is generating sequence numbers. No user, however, can ever acquire the sequence number that is generated by another user.
CREATE SEQUENCE <seq_name>
[ AS <datatype> ]
[ START WITH <start_value> ]
[ INCREMENT BY <increment> ]
[ NO MINVALUE | MINVALUE <minimum_value> ]
[ NO MAXVALUE | MAXVALUE <maximum_value> ]
[ NO CYCLE | CYCLE ];
|The name of the sequence.
|The data type. The value can be any exact integer type such as byteint, smallint, integer, or bigint. If you do not specify this option, the default data type is bigint. For a description of the data types and their ranges, see Table 1.
|The starting value. Use this clause to start an ascending sequence
at a value greater than its minimum or to start a descending sequence
at a value less than its maximum.
For ascending sequences, the
default value is the minimum value of the sequence. For descending
sequences, the default value is the maximum value of the sequence.
This integer value must be between the sequence data type
|The increment value. The integer value can be any positive
or negative integer, but it cannot be zero. The magnitude of this
value must be less than the difference of the
If you specify a positive value, you create an ascending sequence. If you specify a negative value, you create a descending sequence. If you do not specify this option, the default is 1.
|NO MINVALUE | MINVALUE
|The minimum value of the sequence. The default is NO MINVALUE,
which is equivalent to specifying a minimum value of 1.
MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
|NO MAXVALUE | MAXVALUE
|The maximum value that the sequence can have. The default is
NO MAXVALUE, which results in the largest value for the specified
MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE. For a description of the data type ranges, see Table 1.
|NO CYCLE | CYCLE
|Whether the sequence continues to generate values after it
reaches either its maximum value (in an ascending sequence) or its
minimum value (in a descending sequence). The default is NO CYCLE,
which means that the sequence stops when it reaches its last value.
If you specify CYCLE, then when an ascending sequence reaches it maximum value, it uses its minimum value next. When a descending sequence reaches its minimum value, it uses its maximum value next.
|The command was successful.
You must be the admin user, the database or schema owner, or your account must have the Create Sequence administration permission.
- Create a sequence as an integer with a starting value of 11, increment
of 2, minvalue of 1, and maxvalue of 100:
CREATE SEQUENCE sequence1 As integer START WITH 11 INCREMENT BY 2 MINVALUE 1 MAXVALUE 100 NO CYCLE;