Use the CREATE SEQUENCE statement to create a sequencer object that generates sequence numbers, see Sequences.
Parameters, clauses, keywords, and variables
▪ DENSE: Guarantees that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, then any sequence number allocations are also rolled back. The drawback of dense sequences is that the sequence is locked out from other transactions until the current transaction ends.
If you do not specify DENSE, sparse sequences are generated. Sparse sequences have unique values but they are not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, the sequence number is simply lost.
▪ START WITH: Specifies the starting number. If not specified, both dense and sparse sequence numbers start from 1.
▪ INCREMENT BY: Specifies the increment between successive sequence values.
Examples
CREATE DENSE SEQUENCE SEQ1; INSERT INTO ORDER (id) VALUES (SEQ1.NEXTVAL); INSERT INTO ORDER (id) VALUES (SEQ1.NEXTVAL); "CREATE PROCEDURE get_my_seq RETURNS (val INTEGER) BEGIN EXEC SEQUENCE my_sequence.NEXT INTO (val); END";