CREATE SEQUENCE statement

Creates a sequence that can be used to generate primary key values that are unique across multiple tables, and for generating default values for a table. This statement applies to SAP Sybase IQ catalog store tables only.

Syntax

CREATE [ OR REPLACE  ] SEQUENCE [ owner.] sequence-name
[ INCREMENT BY signed-integer ]
[ START WITH signed-integer ]
[ MINVALUE signed-integer  |  NO MINVALUE  ]
[ MAXVALUE signed-integer  |  NO MAXVALUE  ]
[ CACHE integer  |  NO CACHE  ]
[ CYCLE  |  NO CYCLE  ]

Parameters

OR REPLACE clause – Specifying OR REPLACE creates a new sequence, or replaces an existing sequence with the same name. If you do not use the OR REPLACE clause, an error is returned if you specify the name of a sequence that already exists for the current user.

INCREMENT BY clause – Defines the amount the next sequence value is incremented from the last value assigned. The default is 1. Specify a negative value to generate a descending sequence. An error is returned if the INCREMENT BY value is 0.

START WITH clause – Defines the starting sequence value. If you do not specify a value for the START WITH clause, MINVALUE is used for ascending sequences and MAXVALUE is used for descending sequences. An error is returned if the START WITH value is beyond the range specified by MINVALUE or MAXVALUE.

MINVALUE clause – Defines the smallest value generated by the sequence. The default is 1. An error is returned if MINVALUE is greater than ( 2^63-1) or less than -(2^63-1). An error is also returned if MINVALUE is greater than MAXVALUE.

MAXVALUE clause – Defines the largest value generated by the sequence. The default is 2^63-1. An error is returned if MAXVALUE is greater than 2^63-1 or less than -(2^63-1).

CACHE clause – Specifies the number of preallocated sequence values that are kept in memory for faster access. When the cache is exhausted, the sequence cache is repopulated and a corresponding entry is written to the transaction log. At checkpoint time, the current value of the cache is forwarded to the ISYSSEQUENCE system table. The default is 100.

CYCLE clause – Specifies whether values should continue to be generated after the maximum or minimum value is reached.

The default is NO CYCLE, which returns an error once the maximum or minimum value is reached.

Remarks

A sequence is a database object that allows the automatic generation of numeric values. A sequence is not bound to a specific or unique table column and is only accessible through the table column to which it is applied.

Sequences can generate values in one of the following ways:

Increment or decrement monotonically without bound
Increment or decrement monotonically to a user-defined limit and stop
Increment or decrement monotonically to a user-defined limit and cycle back to the beginning and start again

You control the behavior when the sequence runs out of values using the CYCLE clause.

If a sequence is increasing and it exceeds the MAXVALUE, MINVALUE is used as the next sequence value if CYCLE is specified. If a sequence is decreasing and it falls below MINVALUE, MAXVALUE is used as the next sequence value if CYCLE is specified. If CYCLE is not specified, an error is returned.

Sequence values cannot be used with views or materialized view definitions.

Privileges

You must have the CREATE ANY SEQUENCE or CREATE ANY OBJECT system privilege to create sequences.

Side effects

None

Standards and compatibility

SQL/2008 – Sequences comprise SQL/2008 language feature T176. SAP Sybase IQ does not allow optional specification of the sequence data type—this behavior can be achieved with a CAST when using the sequence.
In addition, the following are vendor extensions:
- CACHE clause
- OR REPLACE syntax
- CURRVAL expression
- Use of sequences in DEFAULT expressions

Example

The following example creates a sequence named Test that starts at 4, increments by 2, does not cycle, and caches 15 values at a time:

CREATE SEQUENCE Test
START WITH 4
INCREMENT BY 2
NO MAXVALUE
NO CYCLE
CACHE 15;