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;

ID NAME
1 Huey
2 Dewey
3 Louie

The magic behind this is of course created by sequences:

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

COLUMN_NAME COLUMN_ID
ID 145210  
NAME 145211

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

SCHEMA_NAME SEQUENCE_NAME SEQUENCE_OID START_NUMBER MIN_VALUE MAX_VALUE INCREMENT_BY IS_CYCLED RESET_BY_QUERY CACHE_SIZE
SYSTEM _SYS_SEQUENCE_145210_#0_# 145215 1 1 4611686018427387903 1 FALSE select max(“ID”)+1 from “SYSTEM”.”SOME_NAMES” 1

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