CREATE SEQUENCE

The CREATE SEQUENCE statement creates a new sequence number generator that can subsequently be used by multiple users to generate unique integers. Use the CREATE SEQUENCE statement to define the initial value of the sequence, define the increment value, the maximum or minimum value and determine if the sequence continues to generate numbers after the minimum or maximum is reached.

Required privilege

CREATE SEQUENCE (if owner) or CREATE ANY SEQUENCE (if not owner)

SQL syntax

CREATE SEQUENCE [Owner.]SequenceName
       [INCREMENT BY IncrementValue]
       [MINVALUE MinimumValue]
       [MAXVALUE MaximumValue]
       [CYCLE]
       [CACHE CacheValue]
       [START WITH StartValue]

Parameters

The CREATE SEQUENCE statement has the parameters:

Parameter	Description
`SEQUENCE` `[Owner.]SequenceName`	Name of the sequence number generator.
`INCREMENT BY` `IncrementValue`	The incremental value between consecutive numbers. This value can be either a positive or negative integer. It cannot be 0. If the value is positive, it is an ascending sequence. If the value is negative, it is descending. The default value is 1. In a descending sequence, the range starts from `MAXVALUE` to `MINVALUE`, and vice versa for ascending sequence.
`MINVALUE` `MinimumValue`	Specifies the minimum value for the sequence. The default minimum value is 1.
`MAXVALUE` `MaximumValue`	The largest possible value for an ascending sequence, or the starting value for a descending sequence. The default maximum value is (2⁶³) -1, which is the maximum of BIGINT.
`CYCLE`	Indicates that the sequence number generator continues to generate numbers after it reaches the maximum or minimum value. By default, sequences do not cycle. Once the number reaches the maximum value in the ascending sequence, the sequence wraps around and generates numbers from its minimum value. For a descending sequence, when the minimum value is reached, the sequence number wraps around, beginning from the maximum value. If `CYCLE` is not specified, the sequence number generator stops generating numbers when the maximum/minimum is reached and TimesTen returns an error.
`CACHE` `CacheValue`	`CACHE` indicates the range of numbers that are cached each time. When a restart occurs, unused cached numbers are lost. If you specify a `CacheValue` of 1, then each use of the sequence results in an update to the database. Larger cache values result in fewer changes to the database and less overhead. The default is 20.
`START WITH` `StartValue`	Specifies the first sequence number to be generated. Use this clause to start an ascending sequence at a value that is greater than the minimum value or to start a descending sequence at a value less than the maximum. The `StartValue` must be greater or equal `MinimumValue` and `StartValue` must be less than or equal to `MaximumValue`.

Description

All parameters in the CREATE SEQUENCE statement must be integer values.
If you do not specify a value in the parameters, TimesTen defaults to an ascending sequence that starts with 1, increments by 1, has the default maximum value and does not cycle.
There is no ALTER SEQUENCE statement in TimesTen. To alter a sequence, use the DROP SEQUENCE statement and then create a new sequence with the same name. For example, to change the MINVALUE, drop the sequence and re-create it with the same name and with the desired MINVALUE.
Do not create a sequence with the same name as a view or materialized view.

Incrementing SEQUENCE values with CURRVAL and NEXTVAL

To refer to the SEQUENCE values in a SQL statement, use CURRVAL and NEXTVAL.

CURRVAL returns the value of the last call to NEXTVAL if there is one in the current session, otherwise it returns an error.
NEXTVAL increments the current sequence value by the specified increment and returns the value for each row accessed.
NEXTVAL and CURRVAL can be used in:
- The SelectList of a SELECT statement, but not the SelectList of a subquery
- The SelectList of an INSERT...SELECT statement
- The SET clause of an UPDATE statement
In a single SQL statement with multiple NEXTVAL references, TimesTen only increments the sequence once, returning the same value for all occurrences of NEXTVAL.
If a SQL statement contains both NEXTVAL and CURRVAL, NEXTVAL is executed first. CURRVAL and NEXTVAL have the same value in that SQL statement.
The current value of a sequence is a connection-specific value. If there are two concurrent connections to the same data store, each connection has its own CURRVAL of the same sequence set to its last NEXTVAL reference.
In the case of recovery, sequences are not rolled back. It is possible that the range of values of a sequence can have gaps. Each sequence value is still unique.
When the maximum value is reached, SEQUENCE either wraps or issues an error statement, depending on the value of the CYCLE option of the CREATE SEQUENCE.

Note:

Sequences with the CYCLE attribute cannot be replicated.

Examples

Create a sequence.

CREATE SEQUENCE mysequence INCREMENT BY 1 MINVALUE 2 
       MAXVALUE 1000;

This example assumes that tab1 has 1 row in the table and that CYCLE is used:

CREATE SEQUENCE s1 MINVALUE 2 MAXVALUE 4 CYCLE;
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 3; */
SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 4; */

After the maximum value is reached, the cycle starts from the minimum value for an ascending sequence.

SELECT s1.NEXTVAL FROM tab1;
/* Returns the value of 2; */

To create a sequence and generate a sequence number:

CREATE SEQUENCE seq INCREMENT BY 1;
INSERT INTO student VALUES (seq.NEXTVAL, 'Sally');

To use a sequence in an UPDATE SET clause:

UPDATE student SET studentno = seq.NEXTVAL WHERE name = 'Sally';

To use a sequence in a query:

SELECT seq.CURRVAL FROM student;