Skip to Content

Unable to reset Sequence after delete the data from the table

Hi All,

I have created a table with an 'ID' column and it automatically creates a sequence name "_SYS_SEQUENCE_4231595_#0_#"'.

I know if you delete and reload the data from the table, the sequence won't reset. I am trying to reset the sequence after data is deleted from the table in the procedure.

1. CREATE COLUMN TABLE "ABC" ( ID BIGINT PRIMARY KEY generated by default as IDENTITY,

Material NVARCHAR(100));

2. That's created a sequence "_SYS_SEQUENCE_4231595_#0_#".

3. Delete data from the Table 'ABC'.

4. RESTART the sequence with the syntax:

ALTER SEQUENCE "SCHEMA"."_SYS_SEQUENCE_4231595_#0_#" RESTART WITH 1; But it's throwing below error.

Could not execute 'ALTER SEQUENCE "_SYS_SEQUENCE_4231595_#0_#" RESTART WITH 1' SAP DBTech JDBC: [383]: invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_4231595_#0_#: line 1 col 16 (at pos 15)

5. I am able to reset the sequence which is created from the flow graph(generate serial key) option.

ALTER SEQUENCE "SCHEMA"."EQUFG" RESTART WITH 1;

Would anyone please tell me what I am missing, also I need to use the Restart statement in the procedure later.

I am using the HANA 1.0 Sp12.

Thank you

Sunil Sharma

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 26, 2020 at 06:49 AM

    Nice to see that my old content still gets pulled up to answer questions now and then :)

    Good work, Abhishek Shanbhogue !

    Now, about the question:
    Once you choose to use IDENTIY for your ID column, you hand over control of the sequence to HANA.
    The RESET BY clause will only be executed during instance startup; so that might be an option for you: delete or truncate the table(s) and restart the DB.

    Another option, would be to try and circumvent the protection of system objects by setting a support parameter. This, however, leaves the structural integrity of the HANA instance open for damage, so I'm not recommending this at all.

    Instead, if you do need to be able to control the numbers generated by the sequence, then don't use the fully automatic IDENTITY but a custom sequence.

    IDENTITY and SEQUENCE numbers are meant to provide meaningless numbers in a lock-free way.
    If you feel a need to reset them, then I assume that there, in fact, is some added meaning, like "order of records as they were inserted" or "running number of records in the table". None of those meanings can be supported by IDENTITY or SEQUENCES!
    I highly recommend to review your assumptions about what this number "means" for your data and if you're using the right tool for the job here.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.