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.
Listing 1
1CREATE COLUMN TABLE Employee(
2 EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY,
3 FIRST_NAME VARCHAR(30),
4 LAST_NAME VARCHAR(30)
5)
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
1 ...
2 EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000),
3 ...
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
1 ...
2 EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 2),