Reset AUTO_INCREMENT field in SAP HANA

Question

I'm facing an issue on SAP HANA. I created a table as :

create column table some_names
   (ID bigint not null primary key generated by default as IDENTITY,
   NAME nvarchar(30));

And I inserted some values :

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

Check :

select * from some_names;
 
ID  NAME
1   Huey
2   Dewey
3   Louie

It worked perfectly.

But I'd like to reset the auto_increment of this ID field to '0'. Does someone know how to do it please ?

Answer 1

If you're looking for a per-procedure sequence-dispenser then sequences and the IDENTITY column are likely the wrong choice. Both persist the last guaranteed highest number, in order to be consistent with the stored data. If the numbers are actually volatile and it doesn't matter that the same number is used repeatedly, you should rather go and generate the number sequence yourself.

One way for that could be to use cross joins, LIMIT and the ROW_NUMBER() window function. Another option is to create a table function that returns a table of sequenced numbers:

drop function genNo;
create function genNo (in num_rows bigint) returns table (NULLCOL BIGINT, SEQID BIGINT)
as

begin
    DECLARE NULLCOL  INTEGER ARRAY;
    NULLCOL [:num_rows] = NULL;

    result = UNNEST (:NULLCOL) WITH ORDINALITY AS ("NULLCOL", "SEQID");
    return :result;
end;

With this you can e.g. run

select SEQID
from genNo (1000000)

to get 1000000 numbers in increasing order.