is there a way to create sequence without any gaps in snowflake. I cannot use row number as I am running the updates/inserts multiple times which will end up creating same row numbers 1 to n on every run
First I tried creating sequence. But it is giving random range of numbers on every query update like 101..105 and 220...225 and on next run 380..385
Snowflake does not guarantee to generate sequence numbers with no gaps. Sequences will wrap around after reaching the maximum positive integer value for the data type.
https://docs.snowflake.net/manuals/user-guide/querying-sequences.html
These functions are evaluated in warehouse worker instances in parallel. So when the functions produce sequences, they have to take parallelism into account.
Row_number() could be used as a workaround
https://docs.snowflake.net/manuals/sql-reference/functions/row_number.html
"Snowflake does not guarantee generating sequence numbers with no gaps. Sequences will wrap around after reaching the maximum positive integer value for the data type"
https://docs.snowflake.net/manuals/user-guide/querying-sequences.html#sequence-semantics
Snowflake utilizes an internal sequence to generate the values for the identity column, and it does not guarantee to generate gap-free sequence numbers. This is because of Snowflake caches "sequence values" when they are started to use in a batch insert. Instead of getting one value at a time, it asks for a range of values (e.g. 256, 512, 1024 and so on...) to allow for efficient parallelism. Therefore when inserting more rows, the gap might occur.
You can consider using the ROW_NUMBER window function.
https://docs.snowflake.net/manuals/sql-reference/functions/row_number.html#row-number
Also When you create a new sequence or a new auto-incremented column, you can specify the ORDER or NOORDER parameter to indicate whether or not the sequence can generate new values in increasing order.
ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order (or, if the interval is a negative value, in decreasing order).
For example, if a sequence or auto-incremented column has START 1 INCREMENT 2, the generated values might be 1, 3, 5, 7, 9, etc.
NOORDER specifies that the values are not guaranteed to be in increasing order.
For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.
NOORDER can improve performance when multiple insert operations need to be performed concurrently (for example, when multiple clients are executing multiple INSERT statements).
Default: The NOORDER_SEQUENCE_AS_DEFAULT parameter determines which property is set by default.
Please review the below documentation for more information:
https://docs.snowflake.com/en/release-notes/bcr-bundles/2024_01/bcr-1483
https://docs.snowflake.com/en/sql-reference/sql/create-sequence#optional-parameters
https://docs.snowflake.com/en/sql-reference/parameters#noorder-sequence-as-default
