Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements


Creates a sequence that can be used to generate primary key values that are unique across multiple tables, and for generating default values for a table.

CREATE [ OR REPLACE ] SEQUENCE [ owner.] sequence-name
[ INCREMENT BY signed-integer ]
[ START WITH signed-integer ]
[ MINVALUE signed-integer | NO MINVALUE ]
[ MAXVALUE signed-integer | NO MAXVALUE ]
[ CACHE integer | NO CACHE ]
  • OR REPLACE clause

    Specifying OR REPLACE creates a new sequence, or replaces an existing sequence with the same name. If you do not use the OR REPLACE clause, an error is returned if you specify the name of a sequence that already exists for the current user.

  • INCREMENT BY clause

    Defines the amount the next sequence value is incremented from the last value assigned. The default is 1. Specify a negative value to generate a descending sequence. An error is returned if the INCREMENT BY value is 0.

  • START WITH clause

    Defines the starting sequence value. If you do not specify a value for the START WITH clause, MINVALUE is used for ascending sequences and MAXVALUE is used for descending sequences. An error is returned if the START WITH value is beyond the range specified by MINVALUE or MAXVALUE.

  • MINVALUE clause

    Defines the smallest value generated by the sequence. The default is 1. An error is returned if MINVALUE is greater than ( 2^63-1) or less than -(2^63-1). An error is also returned if MINVALUE is greater than MAXVALUE.

  • MAXVALUE clause

    Defines the largest value generated by the sequence. The default is 2^63-1. An error is returned if MAXVALUE is greater than 2^63-1 or less than -(2^63-1).

  • CACHE clause

    Specifies the number of preallocated sequence values that are kept in memory for faster access. When the cache is exhausted, the sequence cache is repopulated and a corresponding entry is written to the transaction log. At checkpoint time, the current value of the cache is forwarded to the ISYSSEQUENCE system table. The default is 100.

  • CYCLE clause

    Specifies whether values should continue to be generated after the maximum or minimum value is reached.

    The default is NO CYCLE, which returns an error once the maximum or minimum value is reached.


A sequence is a database object that allows the automatic generation of numeric values. A sequence is not bound to a specific or unique table column.

Sequences can generate values in one of the following ways:

  • Increment or decrement monotonically without bound
  • Increment or decrement monotonically to a user-defined limit and stop
  • Increment or decrement monotonically to a user-defined limit and cycle back to the beginning and start again

You control the behavior when the sequence runs out of values using the CYCLE clause.

If a sequence is increasing and it exceeds the MAXVALUE, MINVALUE is used as the next sequence value if CYCLE is specified. If a sequence is decreasing and it falls below MINVALUE, MAXVALUE is used as the next sequence value if CYCLE is specified. If CYCLE is not specified, an error is returned.

Sequence values cannot be used with views or materialized view definitions.


You must have the CREATE ANY SEQUENCE or CREATE ANY OBJECT system privilege to create sequences.

To replace an existing sequence, you must have one of the following:

  • CREATE ANY SEQUENCE and DROP ANY SEQUENCE system privileges.

  • CREATE ANY OBJECT and DROP ANY OBJECT system privileges.

  • ALTER ANY OBJECT or ALTER ANY SEQUENCE system privileges.

Side effects


  • ANSI/ISO SQL Standard

    Sequences comprise SQL Language Feature T176. The software does not allow optional specification of the sequence data type. This behavior can be achieved with a CAST when using the sequence.

    In addition, the following are not in the standard:

    • CACHE clause
    • OR REPLACE syntax
    • CURRVAL expression
    • Use of sequences in DEFAULT expressions


The following example creates a sequence named Test that starts at 4, increments by 2, does not cycle, and caches 15 values at a time: