Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
lbreddemann
Active Contributor

Not sure you knew this already, but starting with SPS 8 (seemed to work with Rev. 74 as well), SAP HANA comes with an option to have an IDENTITY column in column store tables.

The idea here is that you can insert data into the table and every row will automagically get a new unique number assigned.

Of course that's possible e.g. by defining a sequence and use <sequence>.nextval for the INSERT/UPDATE command, but having it "built-in" to the table is kind of neat and can help making data loading easier.

To create such an identity column just use the following syntax:

CREATE COLUMN TABLE <table_name> (<column_name> <num_data_type> GENERATE ALWAYS AS IDENTITY);

CREATE COLUMN TABLE <table_name> (<column_name> <num_data_type> GENERATED BY DEFAULT AS IDENTITY);

Note (edit 27.06.14): the syntax above (stroke out now) was actually only half correct.

Although it technically works it does not yield the correct result, since it would always generate an ID value even if one is provided.

The newly introduced GENERATED BY DEFAULT option for SPS 8 does handle this in a better way. It only generates values if no value is provided and thus the default value should be used. This matters especially for migration use cases where existing data with existing values for the IDENTITY column has to be inserted into the table.

Make sure to not put IDENTITY into any kinds of quotation marks!

For example:

create column table some_names

         (ID bigint not null primary key generated by default as IDENTITY,

          NAME nvarchar(30));

And now we can do things like this:

insert into some_names (name) values ('Huey');

insert into some_names (name) values ('Dewey');      

insert into some_names (name) values ('Louie');   

select * from some_names;

IDNAME
1Huey
2Dewey
3Louie

The magic behind this is of course created by sequences:

select column_name, column_id from table_columns where table_name ='SOME_NAMES'

COLUMN_NAMECOLUMN_ID
ID      145210  
NAME    145211

select * from sequences where sequence_name like '%145210%'

SCHEMA_NAMESEQUENCE_NAME          SEQUENCE_OIDSTART_NUMBERMIN_VALUEMAX_VALUE        INCREMENT_BYIS_CYCLEDRESET_BY_QUERY                            CACHE_SIZE
SYSTEM  _SYS_SEQUENCE_145210_#0_#145215    1        1      46116860184273879031        FALSE  select max("ID")+1 from "SYSTEM"."SOME_NAMES"1      

Aaaaand, that's it :smile:

With this it is even easier to migrate from other platforms to SAP HANA.

There you go - now you know!

Cheers,

Lars

75 Comments