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

To report this post you need to login first.

66 Comments

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

  1. Henk Binnendijk

    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….

    (0) 
    1. Lars Breddemann Post author

      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 😉 .

      (0) 
          1. Gaurav Patel

            ❗ 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?

            (0) 
            1. Lars Breddemann Post author

              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.

              (0) 
      1. Chetan Dave

        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?

        (0) 
        1. Lars Breddemann Post author

          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

          (0) 
          1. Chetan Dave

            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.

            (0) 
  2. Jim Giffin

    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? 

    (0) 
  3. Sean Silvestri

    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,

    Sean

    (0) 
    1. Krishna Tangudu

      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 )

      Regards,

      Krishna Tangudu

      (0) 
    2. Lars Breddemann Post author

      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!):

      NAME

      Huey

      Dewey

      Louie

      */

      IMPORT FROM CSV FILE ‘/tmp/some_names.csv’

          INTO “SOME_NAMES”

          WITH

              RECORD DELIMITED BY ‘\r\n’

              FIELD DELIMITED BY ‘;’

              COLUMN LIST IN FIRST ROW;

         

      select * from some_name;

      ID

      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

      (0) 
            1. Lars Breddemann Post author

              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.

              (0) 
          1. Sean Silvestri

            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.

            thanks,

            Sean

            (0) 
          2. Krishna Tangudu

            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.

            Regards,

            Krishna Tangudu

            (0) 
            1. Lars Breddemann Post author

              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”

                  WITH

                      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.

              Cheers,

              Lars

              (0) 
              1. Krishna Tangudu

                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.

                Regards,

                Krishna Tangudu

                (0) 
  4. Greg Besch

    Lars Breddemann

    Lars,

    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,

    Greg

    (0) 
      1. Thomas Jung

        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(“sap.hana.democontent.epmNext.data”, “EPM.MasterData.Addresses”,

          { ADDRESSID: { $key:  ‘”SAP_HANA_EPM_NEXT”.”sap.hana.democontent.epmNext.data::addressId”‘ }

          });

          // create new Address

          var newAddress = new oAddress({

          CITY:  input[0].City,

          COUNTRY: input[0].Country,

          REGION: input[0].Region,

            });

          newAddress.$save();

        (0) 
  5. Zheng Gong

    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.

    (0) 
  6. Wayne Pau

    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?

    Thanks!

    w.

    (0) 
  7. Selvarasan Subramanian

    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

    Thanks,

    Selva

    (0) 
    1. Lars Breddemann Post author

      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

      (0) 
  8. Mert Karakilic

    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 )

    (0) 
    1. Lars Breddemann Post author

      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.

      (0) 
  9. deba nayak

    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?

    (0) 
      1. deba nayak

        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 ?

        (0) 
        1. Lars Breddemann Post author

          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.

          (0) 
  10. Gopal Anand

    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

    (0) 
  11. Tobias Grein

    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.

    (0) 
    1. Lars Breddemann Post author

      Please open a question with a full description of the problem incl. how to reproduce it and on which HANA revision in the Q&A area.

      (0) 

Leave a Reply