CODEWITHSUNDEEP
hana
Thorsten Niehues
Thorsten Niehues

Reputation: 14452

SAP HANA Sequence reset by Max or Max +1

In SAP HANA we use sequences.

However I am not sure what to define for reset by

do I use select max(ID) from tbl or max(ID) + 1 from tbl?

resently we got an unique constrained violation for the ID field.
And the sequence is defined as select max(ID) from tbl

Also is it even better to avoid the option "reset by"?

Upvotes: 0

Views: 919

Answers (2)

Mike Colegrove
Mike Colegrove

Reputation: 1

The statement you may be looking for to reset the next key to be inserted is ALTER SEQUENCE ASEQ RESTART WITH 5000000;

ASEQ is the SEQUENCE name and the next value to auto insert is 5,000,000.

One big issue with HANA is that when you create a table that auto generates a sequence with prefix SYS which cannot be altered. HANA gives you a message such as [Code: 383, SQL State: HY000] SAP DBTech JDBC: [383] (at 15): invalid identifier: sys prefix not allowed: SYS_SEQUENCE_225390#0_#: line 1 col 16 (at pos 15).

SAP please do something.

A create table statement that would auto generate sequence you cannot alter is one such as the following.

CREATE ROW TABLE "WEBLOG"   ( "KEYTYPE" VARCHAR(8) CS_STRING DEFAULT '  ' NOT NULL, "KEYID" DECIMAL(10) CS_FIXED DEFAULT 0 NOT NULL, "USERID" INT CS_INT NOT NULL, "ROWID" DECIMAL(10) CS_FIXED GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999), PRIMARY KEY ( "ROWID" ) ) ;

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10386

The common logic for the RESET BY clause is to check the current value (max(ID)) and add an offset (e.g. +1) to avoid a double allocation of a key value.

Not using the option effectively disables the ability to automatically set the current sequence value to a value that will not collide with existing stored values.

To provide some context: usually the sequence number generator uses a cache (even though it's not set up by default) to allow for high-speed consumption of sequence numbers. In case of a system failure, the numbers in the cache that have not yet been consumed are "lost" in the sense that the database doesn't retain the information which numbers from the cache had been fetched in a recoverable fashion.

By using the RESET BY clause, the "loss" of numbers can be reduced as the sequence gets set back to the last actually used sequence number.

Upvotes: 1

Related Questions