CREATE SEQUENCE

CREATE SEQUENCE — define a new sequence generator

Syntax

CREATE SEQUENCE [ schema.]seqname [ sequence_option [,...] ]

where sequence_option can be one of the following:

START WITH value
QUANTUM SIZE size

Description

CREATE SEQUENCE creates a new sequence number generator. The sequence database object is owned by the user issuing the CREATE SEQUENCE command. Access privileges for the sequence may be set using GRANT. See also Example 3.

You need to GRANT SELECT rights to a table’s sequence(s) in order for a non-admin user to be able to insert into that table.

The sequence number generator assigns bigint (64-bit integer) values, by default, starting with the value one (1). The START WITH sequence_option can be used to provide a different starting integer value for the sequence. This starting integer value is referred to as the floor value for the sequence.

A sequence will always generate a unique integer value that is greater than or equal to the floor value for the sequence. However, these integer values are not necessarily generated in sequential, ascending order. See Sequences and Multiple TEs for how sequences are managed by Transaction Engine(s).

The sequence value is increased each time a unique integer value is generated by the sequence. This can occur in one of two ways:

  • Assigning values to a column that is defined as being generated by an identity (see CREATE TABLE). Each time a new row is inserted into the table, the sequence generates a new, unique integer value and assigns it to the column value.

  • The NEXT VALUE NuoDB SQL expression can be used to select from a sequence.

Sequences and Multiple TEs

Successive calls to NEXT VALUE return a sequence of unique numbers but not necessarily a sequence of ascending values (as you might expect with other RDBMS such as Oracle or SQL Server). Being unique, the values returned are suitable for defining keys (the primary use of sequence values), but no assumptions should be made about the values of keys in successive INSERTs using the same sequence.

The unusual ordering is due to NuoDB’s distributed architecture.

How it Works

Let’s consider how sequence number generators are managed by one or more Transaction Engines (TE). A sequence number generator will reserve a range of integer values for each TE that requests integer values from a sequence.

  • This range of integer values is reserved, by default, in blocks of one hundred (100) values.

  • The QUANTUM SIZE sequence_option can be used to change this block size.

  • Once all of the integer values that have been reserved for that TE are assigned by the sequence, the next range of integer values is reserved for that TE.

  • In this way a sequence can be used by multiple TEs with minimal contention (the sequence is a single shared resource and a potential hot-spot).

Example

Suppose TE1 and TE2 both use the same sequence.

  • The sequence reserves integer values 1-100 for TE1 and reserves integer values 101-200 for TE2.

  • After the sequence assigns all of the integer values reserved for TE1, the next block that the sequence number generator will reserve for TE1 might contain integer values 201-300.

  • If a client connects to the database via TE1 and selects the NEXT VALUE of the sequence, the integer value 1 is assigned.

  • If the same client creates a second connection to the database, this time to TE2, and the NEXT VALUE of the sequence is selected, the integer value 101 would be returned, and so forth.

  • If another client connect to TE1 and uses the same sequence it will receive the value 2.

  • If the first client now fetches the NEXT VALUE via a connection to TE1 it will receive the value 3.

Thus a multi-threaded client invoking a sequence of NEXT VALUE calls, using different connections in different threads, might receive the values 1, 101, 3, 102, 4, 105 …​

  • The numbers are guaranteed unique but not monotonically increasing.

  • Clients should not try to "guess" what these next values will be, nor make asumptions about them.

  • This is in contrast to other RDBMS like Oracle or SQL Server.

Floor and Ceiling Values

As each TE returns unique integer values, the floor value for that sequence for that TE is incremented. In this way a sequence can be used across multiple TEs with minimal contention.

The sequence number generator maintains an integer value that is greater than any integer value that has been reserved for all TEs.

  • This integer value is referred to as the ceiling value for the sequence.

  • The ceiling value for a sequence is always incremented by one thousand (1000).

  • This incremental size cannot be changed.

  • The initial ceiling value of a new sequence, by default, is 1000.

Once a value that is equal to or greater than 1000 has been reserved for a TE, the ceiling value for the sequence is incremented to 2000.

  • The sequence ceiling value will never decrease.

  • Accessing a sequence on a new TE, possibly after restarting the database, will generate sequence values that are greater than or equal to the sequence ceiling value, even if previous values for the sequence, less than floor, have never been returned to a client.

Parameters

schema

Name of the schema in which the sequence will be created. The default is the current schema as defined by the USE statement. If schema is provided, and the schema does not exist, it will be created. Sequences cannot be created in the SYSTEM schema. This schema is reserved for database metadata.

seqname

Name (optionally schema-qualified) of the sequence. The seqname must be distinct from the name of any other sequence object in the schema in which the SEQUENCE is created.

START WITH value

The optional clause START WITH value allows the sequence to begin at any value. The default starting value is one (1). The starting value can be a number, positive or negative. The starting value can also be an expression that returns an integer value. Some examples of an expression include, but are not limited to:

  • A scalar SQL SELECT statement (see SELECT). This must be enclosed by parentheses. This includes all valid SQL SELECT statements, including statements using aggregate functions, GROUP BY, ORDER BY, LIMIT, etc. The SQL SELECT statement must return a number value.

  • A scalar user defined function.

  • A mathematical formula.

QUANTUM SIZE size

The optional QUANTUM SIZE clause allows you to specify how many unique integer values are included in a block of values that will be reserved for a TE. The default size is 100.

Examples

Example 1: Create a sequence and use that sequence to assign a unique value to each row inserted into a table.
CREATE SEQUENCE seq_hockey_fans START WITH 111;

CREATE TABLE hockey_fans (id   INTEGER GENERATED ALWAYS AS IDENTITY(seq_hockey_fans),
                          name STRING);
INSERT INTO hockey_fans (name) VALUES ('me'), ('you');
SELECT * FROM hockey_fans;
 ID  NAME
 --- -----
 111  me
 112  you

SELECT NEXT VALUE FOR seq_hockey_fans FROM DUAL;
 SEQ_HOCKEY_FANS
 ----------------
        113

INSERT INTO hockey_fans (name) VALUES ('us');
SELECT * FROM hockey_fans;
ID  NAME
 --- -----
 111  me
 112  you
 114  us
Example 2: Create a sequence using a scalar SQL SELECT statement, an expression, or a function (UDF) for a starting value.
DROP SEQUENCE IF EXISTS seq_hockey_next;
SELECT MAX(id) FROM hockey.hockey;
 [MAX]
 ------
   24

CREATE SEQUENCE seq_hockey_next START WITH (SELECT MAX(id) +1 FROM hockey.hockey);

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  25

DROP SEQUENCE IF EXISTS seq_hockey_next;
CREATE SEQUENCE seq_hockey_next START WITH 25+1;

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  26

DROP SEQUENCE IF EXISTS seq_hockey_next;
SET DELIMITER @
CREATE FUNCTION fnc_seq_hockey_next
  RETURNS INTEGER
AS
  RETURN (SELECT MAX(ID) +1 FROM hockey.hockey);
END_FUNCTION;
@
SET DELIMITER ;
CREATE SEQUENCE seq_hockey_next START WITH fnc_seq_hockey_next();

SELECT NEXT VALUE FOR seq_hockey_next FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_HOCKEY_NEXT]
 --------------------------------------
                  25
Example 3: Successfully delegate an INSERT permission on a table with a sequence to a non-admin user.
GRANT INSERT on hockey.hockey_fans to app_user;
GRANT SELECT on hockey.seq_hockey_fans to app_user;