As of SPS8, SAP introduced the ability to create auto generated columns that increment by using IDENTITY. Auto generated columns only apply to Column table types. To create an auto generated column, simply add GENERATED BY DEFAULT AS IDENTITY to the column as shown in Listing 1 below.
If you need to start the identity value from a particular number, you can add START WITH 1000 as an argument to IDENTITY where 1000 is the value to start from.
The identity value increments by 1 when data is inserted into the table but this can be changed using INCREMENT BY 2 where 2 is the value to increment by.
Listing 1
CREATE COLUMN TABLE Employee(
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30)
)
If you need to start the identity value from a particular number, you can add START WITH 1000 as an argument to IDENTITY where 1000 is the value to start from.
Listing 2
...
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000),
...
The identity value increments by 1 when data is inserted into the table but this can be changed using INCREMENT BY 2 where 2 is the value to increment by.
Listing 3
...
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 2),
...
HANA DB Articles
-
If Table Exists Function
In this article, I explain how to create a function to determine if a table exists in SAP HANA.
31 October 2019 - 4662 views -
Connect To HANA DB Using NodeJs
This article explains how to connect to a SAP HANA system using the Node HANA client library.
28 October 2019 - 4093 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 5218 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 4944 views -
Connect To HANA DB Using Java
This article describes how to connect to a HANA DB system using the ngdbc.jar Java driver.
03 November 2017 - 3492 views -
Loading And Unloading Column Table Into And From Memory
This article explains how to load and unload a column table into and from memory.
18 October 2017 - 2103 views -
M_SERVICE_MEMORY System View
This article explains how to view detailed information on memory for HANA services.
01 August 2017 - 3188 views