CREATE SEQUENCE
Valid in: SQL, ESQL
The CREATE SEQUENCE statement creates a new sequence. A sequence is a defined database entity that is used to supply a set of integer values to an application in sequential order according to a set of definition parameters (sequence_options).
The CREATE SEQUENCE statement has the following format:
[EXEC SQL] CREATE SEQUENCE [schema.]sequence_name [sequence_options]
sequence_name
Defines the name of the sequence.
sequence_options
Control how the sequence supplies data when requested by an application. Sequence options can be specified in any order, and none are required.
Any of the following options can be specified in a blank-space separated list:
AS data type
Specifies the data type of the sequence as one of the following:
INTEGER
BIGINT
DECIMAL (with an optional precision, but 0 scale)
Default: INTEGER
Note: Both bigint and integer sequences are managed internally as 64-bit integers.
START WITH number
Specifies the start of the sequence as an integer constant. The default value is 1 for positive sequences (positive increment) and -1 for negative sequences (negative increment). (This option is valid with the CREATE SEQUENCE statement only.)
RESTART WITH number
Specifies a new start value for the sequence. (This option is valid with the ALTER SEQUENCE statement only.)
INCREMENT BY number
Specifies the increment value (positive or negative) that produces successive values of the sequence.
Default: 1
MAXVALUE number
Specifies the maximum value allowed for the sequence.
Defaults:
For positive integer sequences: 2**31-1
For positive bigint sequences: 2**63-1
For positive decimal(n) sequences: 10**(n+1)-1
For negative sequences: -1
NO MAXVALUE / NOMAXVALUE
Specifies that sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).
MINVALUE number
Specifies the minimum value allowed for the sequence.
Default:
For positive sequences: 1
For negative bigint sequences: -2**63
For negative integer sequences: -2**31
For negative decimal(n) sequences: -(10**(n+1)-1)
NO MINVALUE / NOMINVALUE
Specifies that sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).
CACHE number
Specifies the number of sequence values held in server memory. When the supply of numbers is exhausted, Ingres requires a catalog access to acquire the next set.
Default: 20
NO CACHE / NOCACHE
Specifies that sequence values are not to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.
Default: CACHE 20 (when neither CACHE nor NOCACHE are specified), which ensures low catalog overhead.
Note: Gaps between two sequence values may occur: If the DBMS is restarted between two requests of a sequence value, the cached but not used values are lost and the cached values are not written back when shutting down. After restart, the Sequence Cache is empty, so the next value is taken from iisequence.
Note: In a multiple DBMS shared cache environment, sequence values may not be in chronological order. Each DBMS maintains its own Sequence Cache. No value appears twice, but the order of the requested values depends on which DBMS is returning the sequence value.
CYCLE
Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).
Default: NO CYCLE
NO CYCLE / NOCYCLE
Specifies that the sequence is not cycled when the last valid value is generated. An error is issued to the requesting transaction.
ORDER
NO ORDER / NOORDER
These options are included solely for syntax compatibility with other DBMSes that implement sequences, and are not currently supported in Ingres.
Default: NOORDER
SEQUENTIAL / UNORDERED
Specifies whether values are returned sequentially or in unordered sequence. RANDOM is a synonym for UNORDERED. This option is ignored for decimal-based sequences.
Default: SEQUENTIAL
Permissions
You must have CREATE_SEQUENCE privilege.
You need NEXT privilege to retrieve values from a defined sequence. For information on the NEXT privilege, see
GRANT (privilege).
Locking and Sequences
In applications, sequences use logical locks that allow multiple transactions to retrieve and update the sequence value while preventing changes to the underlying sequence definition. The logical lock is held until the end of the transaction.
Related Statements
ALTER SEQUENCE
DROP SEQUENCE
Last modified date: 08/29/2024