SQL Sequences in SAP HANA – The Best Resource for SQL Syntax & Elements

Now, it’s turn for the SQL sequences in SAP HANA. We have covered the topics right from the basics of SQL sequences to the use of sequences in SAP HANA Studio. Let’s quickly start the tutorial.

What are SQL Sequences in SAP HANA?

SQL sequences are integer values which generate automatically on executing SQL statement. A sequence can be any trail of numbers like 100, 101, 102, and so on.

We use SQL sequences in SAP HANA databases and applications where there is a need to generate a unique ID on every new record entered in a database. SQL sequences are incremental list of values that you create as per the requirements of the user.

You can generate employee IDs, serial numbers etc. You can generate them in ascending or descending order. The sequences are stored as database objects specific to schemas in SAP HANA.

There is a separate folder as “Sequences” under a schema node where all the sequences stores related to that schema.

SQL Sequence Syntax

You can create SQL sequences using the statement of the syntax given in this section.

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

Sequences are not a part of a database table but are used in tables and applications to generate automated series of numbers. The sequence statement can return two values, current value or next value.

When you use the keyword CURRVAL, then you will get the current value of a sequence as a result. But, when you use keyword NEXTVAL, you will get the succeeding value (next value) of the current value.

Syntax Elements for SQL Statements

Below are some important syntax elements which are used in the statement to create SQL statements to perform specific tasks.

• <sequence_name> assigns the name of the sequence.
• [<sequence_parameter_list>] specifies one or more sequence parameters.
• START WITH <start_value> assigns the starting value of the sequence.
• INCREMENT BY <increment_value> decides by how much a value will be incremented every time a new record is generated. If our start value is 100 and we set increment value at 2, then the next value will be 102. By default, the increment value sets as 1.
• MAXVALUE <max_value> assigns the maximum value by which a sequence can be increased. The maximum value allowed by the system is 4611686018427387902.
• NO MAXVALUE specifies that there is no preferred maximum value. And so, the maximum value can go up to 4611686018427387902 for ascending order and -1 for descending order.
• MINVALUE<min_value> assigns the minimum value which a sequence can generate. The minimum value allowed by the system is -4611686018427387902.
• NO MINVALUE specifies that there is no preferred minimum value. And so, the minimum value can be 1 in ascending order.
• CYCLE specifies that the sequence will again start with the start_value after reaching the max_value or min_value thus creating a loop or a cycle of values.
• NO CYCLE specifies that the sequence will not restart with the start value after reaching the maximum or minimum value. NO CYCLE is set by default.
• CACHE<cache_size> specifies the range of sequence values to be cached in a node. The cache_size is an integer value.
• NO CACHE specifies that there is no selected range of sequence values to cache. NO CACHE is set by default.
• RESET BY<subquery> directs the system to automatically execute the subquery to restart the sequence values generation upon restarting the database.

How to Use SQL Sequences in SAP HANA Studio?

Now, let us learn how to use the sequences in SAP HANA Studio. You can generate automated sequences as columns in data tables in SAP HANA. In this section, we’ll explain the use of SQL sequences with the help of an example.

1. Open the SAP HANA Studio. Make sure you are working on Administration Console in HANA Studio. To create an SQL statement for sequence, open the SQL editor in SAP HANA Studio.

2. Write the SQL statement to create a sequence. Suppose, we have created a sequence and named it “DTF.DTFSEQUENCE”. And, specified the start value as 100. You can also mention the increment value in the same statement. By default, it is 1. Click on the green Execute button (on top of the editor) to execute this statement.

3. Creating a Sequence

The statement used in our example is:

CREATE SEQUENCE DTF.DTFSEQUENCE START WITH 100

The statement will execute successfully. You can check the successful creation of the sequence by refreshing the “Sequences” node under your schema. You will find the name of the newly created sequence there.

4. Inserting a Sequence in a Table Column

Now, to insert this sequence in a table as a column, write the following script:

Select“DTF”.“DTFSEQUENCE”.nextval,“DTF”.“DTFSTUDENTS”.”NAME” FROM “DTF”.”DTFSTUDENTS”

Here, we have selected our sequence by its name DTF.DTFSEQUENCE, a table (DTFSTUDENTS) from the schema (DTF) and a column (NAME).

5. Execute this statement and you will get a new column with the sequence created as we wanted.

In the table DTFSTUDENTS, a column having sequence of numbers starting from 100 and going on to 150 (if there are total 50 students) generates automatically from the SQL sequence statement. A new sequence number will generate with every new row (student entry in our case) added in the table.

Summary

This was all in our tutorial on SQL sequences in SAP HANA. We hope you found it useful. Here, we learned what are SQL sequences, the syntax of the statement to create sequences and how to actually use them in SAP HANA Studio.

Liked the article? Enter your feedback or queries in the comment section. We will be glad to hear from you.