SAP HANA Tutorial: Create Sequence

By : Mason Garcia Mason Garcia
Hours Updated

What is Sequence?

A sequence is a database object that automatically generates the incremented list of numeric values according to rule as specified in sequence specification.

For example to insert employee number automatically in column (EMPLOYEE_NO) of Table, when a new record is inserted in the table, then we use sequence.

Sequence values are generated in Ascending or Descending order.

Sequences are not associated with tables; they are used by the application. There are two values in sequence –

  • CURRVAL – Provide Current value of Sequence.
  • NEXTVAL – Provide Next value of sequence.

SYNTAX

CREATE SEQUENCE <sequence_name> [<sequence_parameter_list>] [RESET BY <subquery>]

SYNTAX ELEMENTS

ELEMENTS DESCRIPTION
<sequence_name> It is the name of the sequence.
[<sequence_parameter_list>] It specifies one or more sequence parameters.
START WITH <start_value> It describes the starting sequence value.
INCREMENT BY <increment_value> This specifies the value to be incremented from the last value assigned for each time when new sequence value generated. The default is 1.
MAXVALUE <max_value> This specifies maximum value ,which can be generated by the sequence. <max_value> can be between -4611686018427387903 and 4611686018427387902.
NO MAXVALUE When the NO MAXVALUE is specified, for an ascending sequence, the maximum value will be 4611686018427387903 and the minimum value for a descending sequence will be -1.
MINVALUE <min_value> / NO MINVALUE It specifies the minimum value that a sequence can generate. <min_value> can be between -4611686018427387904 and 4611686018427387902. When the NO MINVALUE is used, the minimum value for an ascending sequence is 1
CYCLE CYCLE directive specifies that sequence number will be restarted after it reaches its maximum or minimum value.
NO CYCLE Default option.NO CYCLE directive specifies that sequence number will not be restarted after it reaches its maximum or minimum value.
CACHE <cache_size> / The cache size specifies which range of sequence numbers will be cached in a node. <cache_size> must be unsigned integer.
NO CACHE Default option. NO CACHE directive specifies that the sequence number will not be cached in a node.
RESET BY <subquery> It specifies that during the restart of the database, the database automatically executes the <subquery> and the sequence value is restarted with the returned value.

Example –

We will create a sequence with named DHK_SCHEMA.EMP_NO, which will create incremented value of the sequence by +1 each time, when the sequence is used.

Sequence Script –

CREATE SEQUENCE DHK_SCHEMA.EMP_NO START WITH 100 INCREMENT BY 1.

Here we will use object “sequence” in below example to increment the value of employee no by +1 each time the select query is executed. In the query, the “nextval” can be used for serial number generation or same type of requirement.

Use of Sequence – 

SELECT DHK_SCHEMA.EMP_NO.nextval FROM DUMMY;

OUTPUT – 100,101,102………So on every execution of above select query.

You Might Like: