Snowflake sequence max value and Cycle
NickName:danD Ask DateTime:2022-08-29T21:58:21

Snowflake sequence max value and Cycle

I am trying to create a sequence in snowflake with max value and cycle. However I am not able to find any document where it says how to provide the max value. But again I see in doc it says Snowflake will throw error if threshold is reach.

Can somebody please suggest how to create the below sequence in snowflake.

create or replace sequence myseq as bigint
start with 10000
increment by 1
no min value
maxvalue 2000000
no cycle;

And If any body can suggest what is the max value of sequence in snowflake. If I create it without datatype.

Copyright Notice:Content Author:「danD」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/73529986/snowflake-sequence-max-value-and-cycle

Answers
Sergiu 2022-08-29T14:25:20

The answer is here:\nA sequence value can represent a 64-bit two’s complement integer (-2^63 to 2^63 - 1). If the internal representation of a sequence’s next value exceeds this range (in either direction) an error results and the query fails. Note that this may result in losing these sequence values.\nThis is mentioned on Snowflake docs here.",


More about “Snowflake sequence max value and Cycle” related questions

Snowflake sequence max value and Cycle

I am trying to create a sequence in snowflake with max value and cycle. However I am not able to find any document where it says how to provide the max value. But again I see in doc it says Snowflake

Show Detail

How to create a cyclic sequence in snowflake data warehouse

I am looking for Sequence with a Cycle in Snowflake data warehouse like in Oracle. I guess Snowflake data warehouse doesn't have this built-in. Any idea how to implement ?

Show Detail

How to use MAX VALUE and CYCLE CACHE in MySQL Sequence?

I am trying to convert DB2 sequence to MySQL sequence . This is the query i am using in DB CREATE SEQUENCE MYTEST AS BIGINT START WITH 1 INCREMENT BY 1 MAXVALUE 4611686018427387903 CYCLE CACHE 10...

Show Detail

Update ID if Sequence MAX_VALUE reached

I am using the following TRIGGER to Insert in my TEST Table: create or replace TRIGGER TRG_CYCLE BEFORE INSERT ON TEST_CYCLE FOR EACH ROW BEGIN IF :NEW.LOGID IS NULL THEN SELECT SEQ_CYCLE.nex...

Show Detail

Snowflake - add sequence to an existing column

I have to migrate a SQL server database to Snowflake, most of my dimension tables have an identity column as PK, these columns are then referenced across multiple facts tables. I am planning on c...

Show Detail

Increment Existing Sequence in Snowflake

I'm trying to increment an existing ID field that was generated using sequence in snowflake. For reference here's the code & sample output: Code CREATE OR REPLACE SEQUENCE id_sequence START WIT...

Show Detail

SNOWFLAKE Sequence Creation Issue

In SNOWFLAKE database, I was trying to create 2 sequences under same name but with case sensitive and for one sequence I was trying to increment by 1 and the other sequence I was trying to increment

Show Detail

Maximum value allowed for CACHE in CYCLE sequence

CREATE SEQUENCE demo_seq START WITH 1 INCREMENT BY 3 MINVALUE 1 MAXVALUE 14 CYCLE CACHE (?); According to formula i.e. (CEIL(MAXVALUE-MINVALUE))/ABS(INCREMENT) So, (CEIL(14-1))/ABS(3) Th

Show Detail

sequence associated with a snowflake table

For any snowflake table how can I find the sequence associated with all its columns? I tried to look into INFORMATION_SCHEMA but couldn't find any clue. Thanks in advance!

Show Detail

Snowflake Stored Procedures to generate unique IDs and then insert values into a table

I am using Snowflake stored procedures to try and log errors as they occur. The current stored proc has an if/else statement, but I'm just trying to get one section working for now since the code i...

Show Detail