CREATE SEQUENCE

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 specific sequences, do the following:

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.

Syntax

Syntax for creating a sequence:

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 ];

Inputs

The CREATE SEQUENCE command takes the following inputs:

Table 1. CREATE SEQUENCE inputs
Input	Description
<seq_name>	The name of the sequence.
<datatype>	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.
START WITH	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 `minvalue` and `maxvalue`.
INCREMENT BY	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 `minvalue` and `maxvalue`. 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 data type. 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.

Output

The CREATE SEQUENCE command produces the following output:

Table 2. CREATE SEQUENCE output
Output	Description
`CREATE SEQUENCE`	The command was successful.

Privileges

You must be the admin user, the database or schema owner, or your account must have the Create Sequence administration permission.

Usage

The following provides sample usage:

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;
```