Skip to Content

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;

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’

ID      145210  
NAME    145211

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

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!



You must be Logged on to comment or reply to a post.
    • Hi,

      please help me in creating a auto sequence number in the record from Sapui5 application.

      I have a sequence created in hana and creating the record using the xsodata witha create procedure call.

      I am unable to create the application.

      A detail code will be helpful.




  • Just what i am looking for, thanks for the info Lars!

    Is it possible to manually increase such a sequence? With migrations it is hardly the situation a sequence starts with 1....

    • Coincidentally a colleague asked basically the  same question just recently.

      I did not find a way to solve that until another colleague - sometimes really nice to have so many 🙂 of them - brought up the option to specify the starting point for the sequence at definition time.

      create column table some_names

      (ID bigint not null primary key  generated by default as IDENTITY (start with xyz),

      NAME nvarchar(30));

      This way you can load your existing data with an ID up to a value of XYZ and for any newly inserted records the sequence will take over.

      This is supposed to work as of SPS 8. For Rev 74 you'd have to try it out yourself 😉 .

          • ❗ Caution:

            On rev 74 the identity column only retains values will the server is restarted. If you check the sequence definition, as shown in Lars' blog, there is a RESET BY clause.

            This system generated sequence cannot be modified. The outcome is that on server restart values in the identity column are lost.

            Anyone else seeing the same behavior?

          • Hmm... any RESET BY clause would only affect the current value of the sequence, but not the columns that are filled based on the sequence.

            You may want to open a support incident for this.

      • Thanks Lars,

        IDENTITY columns in tables are Implemented as per your suggestion i.e. "GENERATED BY DEFAULT AS IDENTITY"

        Now, I am writing Stored Procedure where want to update ID of Table1 into Table2. In SQL Server this can be done by SCOPE_IDENTITY().

        Please suggest how this can be achieved into SAP HANA?

        • Hey Chetan

          personally I have little experience with MS SQL Server and based on SCOPE_IDENTITY (Transact-SQL) I am under the impression that the SCOPE_IDENTITY() feature is experimental at the moment. What it seems to do is to return the last inserted value for the IDENTITY column wihthin the current execution context.

          Practically speaking it seems to deliver the value that <sequence name>.currval would deliver if you would be using a sequence to generate the IDs.

          Since SAP HANA's IDENTITY column is based on a sequence, my suggestion would be to leverage this and try to query the sequence for the table. Alternatively, you may query the maximum of the column manually.

          - Lars

          • Lars Thanks for your quick response,

            As we are migrating from SQL Server to SAP HANA, support to retrieve inserted ID through IDENTITY option is critical (without Sequence). Trust we will get this is future release

            Also, we can not query Maximum column manually as we have concurrent users.

            Dave C.

  • Thanks for the information Lars - follow up question:  Can we put this IDENTITY into a hdbtable file?   I don't see anything in the Developer Guide information about using it in hdbtable.   

    Is there a way we can put this into our design-time repository? 

  • Hi Lars,

    I had a question for you regarding the identity feature in hana.

    I’ve defined an identity column as outlined in your article.

    I’m doing an IMPORT not an INSERT and the csv file does not contain a column for the identity defined in my hana table.

    The issue is that the IMPORT is attempting to insert the first column of my file into the identity column and is failing.

    I’ve searched and searched an the documentation is quite spotty and not found any information regarding how to handle this.

    Many thanks,


    • Think when using IMPORT , IDENTITY won't work. Even DEFAULT value also wont work. ( It was not working for us when we defined a column with a default value and tried to use IMPORT to load all the columns except the last one. It took null instead of default value )


      Krishna Tangudu

    • This works for me:

      drop table some_names;

      create column table some_names

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

                NAME nvarchar(30));


      FILE CONTENTS (Windows format with \r\n line endings!):






      IMPORT FROM CSV FILE '/tmp/some_names.csv'

          INTO "SOME_NAMES"


              RECORD DELIMITED BY '\r\n'

              FIELD DELIMITED BY ';'



      select * from some_name;


      1 Huey
      2 Dewey
      3 Louie

      I didn't get the front end UI importer to just ignore the ID column, so no idea if that would work - but the IMPORT FROM command does the trick.

      - Lars

          • It works as well, if you provide the column names in the command and don't have them in the file or skip the header line.

            As you didn't post the error message you got I cannot tell why it didn't work for you.

            But anyway - there you got your solution to your problem.

          • I assumed I had to include the columns in the file, which worked.

            How would I include them in the import command. It worked for me by adding the columns to the first line of the file, but i've never had to do that before.



          • I meant DEFAULT value. Like in the table say I had 5 columns, the last column (Ex: Date column) is defined a default value (9999-12-31).

            In the file we have data for 4 columns and expected that it would load default value for the DATE column and instead it loaded NULL.


            Krishna Tangudu

          • Well, try it out 🙂

            Ok,I'll be nice and also post this one:

            -- I add another column with a default value

            alter table some_names add (name_date varchar(10) default '9999-12-31' not null);

            -- I import the same data again

            IMPORT FROM CSV FILE '/tmp/some_names.csv'

                INTO "SOME_NAMES"


                    RECORD DELIMITED BY '\r\n'

                    FIELD DELIMITED BY ';'

                    COLUMN LIST IN FIRST ROW;

            select * from some_names;


            ID  NAME    NAME_DATE

            1   Huey    9999-12-31

            2   Dewey   9999-12-31

            3   Louie   9999-12-31

            4   Paul    9999-12-31

            5   Huey    9999-12-31

            6   Dewey   9999-12-31

            7   Louie   9999-12-31


            -- note: PAUL was entered separately and has nothing to do with the example

            -- also: since I specified NOT NULL for the NAME_DATE SAP HANA automatically

            -- assigned the DEFAULT column value also to the existing values.



          • Thank you so much for your time Lars 🙂 . NOT NULL was one thing I didn't try and because of that it was taking null as valid value and skipping default value. I missed it my bad.  Thanks once again for clarifying.


            Krishna Tangudu

  • Another question, I just truncated a table but the identity just picks up where it left off e.g. it does not start at 1 again...

    Any tips or tricks on this?

  • I generated one odata service for above table. Through that odata service how i insert the data through rest client server or postman server.

    please help me

  • Lars Breddemann


    Would you kindly share information on the correct syntax for using the IDENTITY column in design-time table definition files, please? 

    I've extensively searched the documentation and can't find anything that would give a clue as to whether or not IDENTITY is even supported.  I tried using it in a CDS definition file, but received errors saying "unknown entity".  I would be very grateful for assistance!

    Kind Regards,


      • Identity column is not yet supported in design time tables (HDBDD or HDBTABLE).  You can use a sequence instead.  Planned for the upcoming SPS09 is a library called XSDS to support reuse of CDS Entity definitions in XSJS layer.  When using this library you can just attach a sequence to an entity when you import it and then it works automatically much like an Identity column.  Here is an example:

        var oAddress = XSDS.$importEntity("", "EPM.MasterData.Addresses",

          { ADDRESSID: { $key:  '"SAP_HANA_EPM_NEXT".""' }


          // create new Address

          var newAddress = new oAddress({

          CITY:  input[0].City,

          COUNTRY: input[0].Country,

          REGION: input[0].Region,



  • Hi,

    Nice to know that there is such an option for IDENTITY generation. But is there a way to generate the identity within specific range? (MIN and MAX value of the sequence) ?


  • Hi Lars,

    We got a very strange issue here.

    The table's sequence will be broken randomly. The symptom is the the field ID in all the record become null, and the sequence is broken. So I have to drop the table and recreate it.

  • Hi Lars,

    Your post was great!

    I was using IDENTITY column and ran into an issue with OData Service Definition mapping. Basically the OData service was forcing the JSON to have IDENTITY column on CREATE, even though via SQL INSERT it was not necessary.

    Eventually I found a way around it with Modification Exists, but I am not sure it's the most efficient way.

    OData Service Definition Modification Exist for Tables with an IDENTITY Column

    If anyone knows a better way, please post a comment in the link above?



  • Hi Lars,

    I tried creating the unique column on #Local temporary table for one of  Stored procedures.

    i'm getting error as "feature not supported: cannot create row table having identity column"

    I think its not supported for row store. can you pls suggest

    We are on SPS9



    • I suppose you're right.

      It's what the error message explicitly states: it's not supported.

      However, for temporary tables you can always just modify your insert statement to include the reference to a regular sequence.

      - Lars

  • I guess the reason why they use the ID in the sequence name instead of the actual column name is in case user updates the column name to something else perhaps?

    Also, any idea why they went for such complicated reset query for the sequence, why not use max(ID)+1 do you think? Here is an example for a column called "ID";

    MAP( mod((max( "ID") - (1) ),1),  0, max("ID") +1,  max("ID") - mod((max( "ID") - (1)), 1 ) +1 )

    • Not sure what you mean with your comment on the sequence name - it doesn't even contain "ID".

      The reset statement however is generated only at column creation and will fail if you rename the column (at least that was the case last time I checked).

      The "weird" statement is most likely given in its current form to handle cases such as an empty table and a NULL result.

  • Hi Lars,

    I want my ID to start from 1 for every load because I am deleting the table again n again (as its a temp table). But Instead my ID is starting from where the ID for the last insert finished (even after deleting the table).

    How to fix this?

      • Yes I tried But getting the below error

        Could not execute 'ALTER SEQUENCE _SYS_SEQUENCE_978778_#0_# RESTART WITH 1'

        SAP DBTech JDBC: [383]: invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_978778_#0_#.

        Am I giving the sequence name incorrectly ?

        • If you want this kind of control about the sequence, IDENTITY column is not the feature to use. Use your own sequence instead and use seq.nextval during inserts to assign the values.

  • Hi with using the above mentioned query i am getting the following error

     SAP DBTech JDBC: [7]: feature not supported: cannot create row table having identity column: ID: line 1 col 16 (at pos 15)

    How to create Auto increment column in HANA DB

  • Hi Lars,

    I have the problem that the auto - id (that you described on Nov. 12 2014) isn't working for me. I am importing data from csv files via ctl files into the column store tables.

    I get the following error: SAP DBTech JDBC: [487]: some of rows have failed to be imported: Partial failure occurs in importing.

    While checking the table content you can see that the ID doesn't start from 1 and that not all rows are imported.

  • Hello Lars,

    I created a table with an ID column that is filled automatically by 'generated by default as identity' (which uses a sequence).

    If the table is filled with data and reaches the limit of the ID field it is not possible to insert new records.

    The problem is that even after deleting records from that table it is not possible to insert new records because the sequence does not get reseted.

    The sequence is generated by the system and is defined with a 'reset by' clause, which is executed automatically when the database gets restarted.

    Is there a way to reset this sequence without restarting the database?

    Sample SQL :

    create column table T (

    ID smallint generated by default as identity,

    WERT integer




    declare i integer;

    for i in 1..32767 do

    insert into T (WERT)

    values (:i*100);

    end for;


    delete from T where WERT>200;

    insert into T (WERT)

    values (300)


    • That question has been asked and answered many times already - using the search option is open to you as well!

      So, I leave it to you to do the minimal required research on that.

  • Hi All ,

    Have a question on , i have 1000 of tables like below

    COMMENT ON TABLE "X"."TEST_PROFESSION" is 'rhs Profession'

    We have decided to partition these tables as they are almost reaching 2 billion records and my question is for doing partition we should not have IDENTITY columns and now what is the best way to remove IDENTITY columns from the table and have the same primary key and data ??

    Many Thanks