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 ?

A simple google search leads to multiple approaches - have you tried any of them? — vwegert, Jul 04 '16 at 07:38
Thanks for your answer. The fact is that I checked on Google but I found nothing that could help me on this task, and most of results concerned other DBMS. :( I checked also on SO but I didn't fint anything on it. — KrasshX, Jul 04 '16 at 08:07
Do you need to start with 0 from the very beginning or restart the sequence later at some point of time? — Shidai, Jul 04 '16 at 08:40
I'd like to reset the auto-increment each time I execute a stored procedure (so I'd like my first line of the stored procedure to be like `alter column ID set auto_increment=0...` or something like that) but I don't know how to do it. — KrasshX, Jul 04 '16 at 08:50
Ok I found that an `auto_increment field` is a `sequence`. I can display my current `id` by doing this : — KrasshX, Jul 04 '16 at 09:28
`select column_name, column_id from table_columns where table_name ='some_names'; --> some_names.ID : 28180356 ` — KrasshX, Jul 04 '16 at 09:29
`select "INTERNAL_1S79"."_SYS_SEQUENCE_28180356_#0_#".CURRVAL from dummy; --> CURRVAL = 3` — KrasshX, Jul 04 '16 at 09:31

score 0 · Accepted Answer · answered Jul 06 '16 at 05:49

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.

score 0 · Answer 2 · answered Aug 29 '22 at 15:52

You can create a Column Table with identity column using the "GENERATED ALWAYS AS IDENTITY (MINVALUE 0 START WITH 0)" option and then you would obtain 0 as your first value at the beginnig of the process. Then before run your process again you can DROP your table and then re-create it again obtaininig the same result always.

score -2 · Answer 3 · answered Jul 04 '16 at 13:49

-2

You Should go with generic sequence. And the code below should suffice:

DROP SEQUENCE SEQ_A;
CREATE SEQUENCE SEQ_A START WITH 0 MINVALUE 0;  

DROP TABLE "TABLE_A";
CREATE COLUMN TABLE "TABLE_A" (
    ID BIGINT,
    NAME nvarchar(30));

INSERT INTO "TABLE_A" (ID,NAME) VALUES (SEQ_A.NEXTVAL,'Shidai');
INSERT INTO "TABLE_A" (ID,NAME) VALUES (SEQ_A.NEXTVAL,'KrasshX');  
SELECT * FROM "TABLE_A";
ALTER SEQUENCE SEQ_A RESTART WITH 0;

answered Jul 04 '16 at 13:49

Shidai

229
1
8

This approach works only, if only a single session uses the sequence at any given time. As soon as two or more sessions rest the sequence, none of the sessions gets the expected numbers. – Lars Br. Jul 06 '16 at 08:54

Reset AUTO_INCREMENT field in SAP HANA

3 Answers3