CREATE SEQUENCE — define a new sequence generator
CREATE SEQUENCE [ schema.]seqname [ sequence_option [,...] ]
sequence_option can be one of the following:
START WITH value QUANTUM SIZE size
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).
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.
NEXT VALUENuoDB 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.
sequence_optioncan 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).
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 VALUEof 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 VALUEof 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 VALUEvia 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 valuefor the sequence.
The ceiling value for a sequence is always incremented by one thousand (1000).
This incremental size cannot be changed.
ceiling valueof 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.
ceiling valuewill 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.
Name of the schema in which the sequence will be created. The default is the current schema as defined by the
schemais provided, and the schema does not exist, it will be created. Sequences cannot be created in the
SYSTEMschema. This schema is reserved for database metadata.
Name (optionally schema-qualified) of the sequence. The
seqnamemust be distinct from the name of any other sequence object in the schema in which the
START WITH value
The optional clause
START WITH valueallows the sequence to begin at any value. The default starting
valueis one (1). The starting
valuecan be a number, positive or negative. The starting
valuecan also be an expression that returns an integer value. Some examples of an expression include, but are not limited to:
A scalar SQL
SELECT). This must be enclosed by parentheses. This includes all valid SQL
SELECTstatements, including statements using aggregate functions,
LIMIT, etc. The SQL
SELECTstatement must return a number value.
A scalar user defined function.
A mathematical formula.
QUANTUM SIZE size
QUANTUM SIZEclause 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
- 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;