Quick note on IDENTITY column in SAP HANA

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;

The magic behind this is of course created by sequences:

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

select * from sequences where sequence_name like ‘%145210%’

Aaaaand, that’s it 🙂

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

There you go – now you know!

Cheers,

Lars