Issue with snowflake insert data using jdbc with auto increment column

Question

I am trying to insert data into a table using jdbc with auto increment column in snowflake from another table. I have skipped the auto increment column and inserting only required column(s) as from the following query

// both tables ddl
CREATE or replace myTargetTable(col1 number(38,0) not null autoincrement,col2 text,constraint conname primary key (col1));
// insert
insert into myTargetTable (COL2) select COL2 from mySrcTable;

It inserts the data correctly with correct auto increment sequence, but the auto increment value takes a random increment in its value if I insert another value

insert into myTargetTable (COL2) values ('randomVal');

It reflects this issue,

Row        Col1      Col2
1,374       1374        sd
1,375       1375        sd
1,376       1376        sd
1,377       1793        randomVal       - manuel insert to see sequence value

Col1 is auto incr

This issue comes with 260 records+. Is this an issue with auto increment key, please help. Thanks!

PS. This issue also comes if col1 is not primary key, and executing queries on snowflake console.

Answer 1

Snowflake utilizes a sequence to generate the values for the identity column:

https://docs.snowflake.com/en/sql-reference/sql/create-table.html#optional-parameters

And Snowflake does not guarantee generating sequence numbers with no gaps:

https://docs.snowflake.com/en/user-guide/querying-sequences.html

You can observer the same behaviour if you create a sequence manually for your column:

create or replace sequence seq1;

CREATE or replace table myTargetTable(col1 number(38,0) not null 
default seq1.nextval,col2 text,constraint conname primary key (col1));

The main issue here is, Snowflake caches "sequence values" when you start to use them in a batch insert. So instead of getting one value at a time, it asks for a range of values (256, 512, 1024 and so on...) Therefore if you insert more rows, you will see the gap will be higher. There is no way to release these values back to the sequence, so this is why you see this huge gap.

Answer 2

Snowflake AUTOINCREMENT uses sequences, and as you can see from the documentation, numbers can and do get "skipped".

There is no guarantee that values from a sequence are contiguous (gap-free) or that the sequence values are assigned in a particular order. There is, in fact, no way to assign values from a sequence to rows in a specified order other than to use single-row statements (this still provides no guarantee about gaps).

https://docs.snowflake.com/en/user-guide/querying-sequences.html