SAP HANA Tutorial: Create Sequence

By : Mason Garcia Mason Garcia
Hours Updated

⚡ Smart Summary

Create Sequence in SAP HANA defines a database object that generates an incremented list of numeric values automatically. Parameters control the start value, increment, limits, cycling, and caching behaviour of the generator.

  • 🔢 Core Definition: A sequence generates numbers according to the rule set in its specification, ascending or descending.
  • 🔗 No Table Binding: Sequences are independent objects used by the application rather than attached to any table.
  • 📍 Two Values: CURRVAL returns the current value and NEXTVAL advances the sequence and returns the new one.
  • 🎚️ Key Parameters: START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, and CACHE shape the output.
  • ♻️ Cycling: CYCLE restarts numbering at the limit; NO CYCLE is the default and raises an error instead.
  • ⚡ Caching: CACHE reserves a block of numbers per node for speed; NO CACHE is the default.
  • 🔄 Reset Option: RESET BY runs a subquery on database restart and resumes from the value it returns.
Read More

Create sequence in SAP HANA

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 … and so on for every execution of the above select query.

RELATED ARTICLES

How to Use a Sequence in a Table

The example above returns a number but does not store it anywhere. Three patterns connect a sequence to real data, and the right choice depends on how much control the application needs.

1) Call NEXTVAL directly in the INSERT. The most explicit approach. The application decides when a number is consumed.

CREATE COLUMN TABLE DHK_SCHEMA.EMPLOYEE (
  EMPLOYEE_NO INTEGER NOT NULL,
  EMP_NAME    NVARCHAR(100),
  PRIMARY KEY (EMPLOYEE_NO)
);

INSERT INTO DHK_SCHEMA.EMPLOYEE
  VALUES (DHK_SCHEMA.EMP_NO.NEXTVAL, 'Anita Sharma');

2) Use the sequence as a column default. The number is assigned even when the insert does not mention the column, which suits applications that were never written to know about the sequence.

CREATE COLUMN TABLE DHK_SCHEMA.EMPLOYEE2 (
  EMPLOYEE_NO INTEGER DEFAULT DHK_SCHEMA.EMP_NO.NEXTVAL,
  EMP_NAME    NVARCHAR(100)
);

INSERT INTO DHK_SCHEMA.EMPLOYEE2 (EMP_NAME) VALUES ('Ravi Kumar');

3) Read the value just issued. CURRVAL returns the last number this session took, which is how a child row is linked to the parent just inserted.

SELECT DHK_SCHEMA.EMP_NO.CURRVAL FROM DUMMY;

⚠️ Warning: CURRVAL fails with an error until NEXTVAL has been called at least once in the current session. It reports the value issued to this session, not the global maximum, so two sessions can hold different current values at the same moment.

Sequence vs Identity Column

SAP HANA also offers an identity column, which generates numbers without a separate object. The two solve the same problem differently.

Parameter Sequence Identity column
Object type Independent database object Column property of one table
Shared across tables Yes, any number of tables can draw from it No, it belongs to a single column
Value visible before insert Yes, through NEXTVAL No, generated during the insert
Definition CREATE SEQUENCE GENERATED BY DEFAULT AS IDENTITY
Best for Shared numbering, parent and child inserts A simple surrogate key on one table

Choose a sequence when the number must be known before the row exists, or when several tables share one numbering series. Choose an identity column for a plain surrogate key with no wider requirement.

Managing Sequences: ALTER, DROP and Gaps

A sequence rarely stays as first created. Three maintenance tasks come up repeatedly.

Changing the behaviour. ALTER SEQUENCE adjusts the increment, limits, or cycling of an existing sequence without dropping it. RESTART WITH resets the counter, which is the usual step after a test data reload.

ALTER SEQUENCE DHK_SCHEMA.EMP_NO RESTART WITH 1000;
ALTER SEQUENCE DHK_SCHEMA.EMP_NO INCREMENT BY 10;
DROP SEQUENCE DHK_SCHEMA.EMP_NO;

Understanding gaps. Sequence numbers are not guaranteed to be contiguous, and this surprises people who expect an unbroken audit trail. Gaps appear for three reasons:

  • Rolled back transactions. NEXTVAL is not transactional. A number consumed by an insert that is later rolled back is never reissued.
  • Caching. With CACHE 100 a node reserves a hundred values at once. A restart discards whatever remains unused in that block.
  • Concurrent sessions. Two sessions inserting simultaneously interleave their numbers, so neither sees a continuous run.

The practical rule is to treat a sequence as a source of unique identifiers, never as a counter of how many rows exist. Where an unbroken series is a legal requirement, such as invoice numbering, it must be generated by application logic with its own controls rather than by a database sequence.

Finding what exists. The system view SEQUENCES lists every sequence with its current value, which is the quickest way to audit a schema. Related syntax is covered in SAP HANA SQL and column definitions in SAP HANA data types.

FAQs

Rolled back transactions, cached blocks lost on restart, and concurrent sessions all create gaps. A sequence guarantees uniqueness, never a continuous run of values.

Yes. A sequence is independent of any table, so several tables can draw from it. This is useful when identifiers must be unique across an entire schema rather than per table.

Yes. Analysing insert throughput shows how many values a node consumes between restarts, which balances the speed gain of caching against the size of the gaps it creates.

Yes. Comparing current values against MAXVALUE and consumption rate predicts exhaustion, so a sequence is widened before inserts start failing in production.

Because NEXTVAL has not been called in the current session. CURRVAL reports the value issued to this session, so it has nothing to report until one has been taken.

Summarize this post with: