CREATE SEQUENCE

The CREATE SEQUENCE statement creates a sequence generator, which is a mechanism for generating exact numeric values, one at a time.

Syntax

CREATE SEQUENCE
   [ SQL Identifier ]
   [ sequenceElement ]*

The sequence name is composed of an optional schemaName and a SQL Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified sequence name is specified, the schema name cannot begin with SYS.

schemaName

The name of the schema to which this sequence belongs. If you do not specify a schema name, the current schema is assumed.

You cannot use a schema name that begins with the SYS. prefix.

SQL Identifier

The name of the sequence

sequenceElement

{
  AS dataType
   | START WITH startValue
   | INCREMENT BY incrementValue
   | MAXVALUE maxValue | NO MAXVALUE
   | MINVALUE minValue | NO MINVALUE
   | CYCLE | NO CYCLE
}

dataType

If specified, the dataType must be an integer type (SMALLINT, INT, or BIGINT). If not specified, the default data type is INT.

startValue

If specified, this is a signed integer representing the first value returned by the sequence object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object.

The default start value for a new ascending sequence object is the minimum value. The default start value for a descending sequence objest is the maximum value.

incrementValue

If specifed, the incrementValue is a non-zero signed integer value that fits in a DataType value.

If this is not specified, the INCREMENT defaults to 1. If incrementValue is positive, the sequence numbers get larger over time; if it is negative, the sequence numbers get smaller over time.

minValue

If specified, minValue must be a signed integer that fits in a DataType value.

If minValueis not specified, or if NO MINVALUEis specified, then minValuedefaults to the smallest negative number that fits in a DataType value.

maxValue

If specified, maxValue must be a signed integer that fits in a DataType value.

If maxValueis not specified, or if NO MAXVALUEis specified, then maxValuedefaults to the largest positive number that fits in a DataType value.

Note that the maxValue must be greater than the minValue.

CYCLE

The CYCLE clause controls what happens when the sequence generator exhausts its range and wraps around.

If CYCLE is specified, the wraparound behavior is to reinitialize the sequence generator to its START value.

If NO CYCLE is specified, Splice Machine throws an exception when the generator wraps around. The default behavior is NO CYCLE.

To retrieve the next value from a sequence generator, use a  NEXT VALUE FOR expression.

Usage Privileges

The owner of the schema where the sequence generator lives automatically gains the USAGE privilege on the sequence generator, and can grant this privilege to other users and roles. Only the database owner and the owner of the sequence generator can grant these USAGE privileges. The USAGE privilege cannot be revoked from the schema owner. See  GRANT statement and  REVOKE statement for more information.

Performance

To boost performance and concurrency, Splice Machine pre-allocates ranges of upcoming values for sequences. The lengths of these ranges can be configured by adjusting the value of the derby.language.sequence.preallocator property.

Examples

The following statement creates a sequence generator of type INT, with a start value of -2147483648 (the smallest INT value). The value increases by 1, and the last legal value is the largest possible INT. If NEXT VALUE FOR is invoked on the generator after it reaches its maximum value, Splice Machine throws an exception.

splice> CREATE SEQUENCE order_id;
0 rows inserted/updated/deleted

This example creates a player ID sequence that starts with the integer value 100:

splice> CREATE SEQUENCE PlayerID_seq 
   START WITH 100;
0 rows inserted/updated/deleted

The following statement creates a sequence of type BIGINT with a start value of 3,000,000,000. The value increases by 1, and the last legal value is the largest possible BIGINT. If NEXT VALUE FOR is invoked on the generator after it reaches its maximum value, Splice Machine throws an exception.

splice> CREATE SEQUENCE order_entry_id
   AS BIGINT
   START WITH 3000000000;
0 rows inserted/updated/deleted

See Also