I'm trying to create a sequence into Snowflake for a table ID, I need it to increase 1 by 1 (1,2,3,4,...), how ever when I create it and try sequence.nextval it increases (1,101,201,301...) and I don't know why.
I'm using the next code to create the sequence (I'm using sysadmin and the correct database)
CREATE OR REPLACE SEQUENCE SEQ_AUTHOR_UID
START WITH 1
INCREMENT BY 1;
COMMENT= 'Use this to fill in AUTHOR_UID';
Also I tried with:
CREATE SEQUENCE SEQ_AUTHOR_UID
START = 1
INCREMENT = 1
COMMENT = 'Use this to fill in AUTHOR_UID';
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
To get the sequence order, use order parameter.
CREATE OR REPLACE SEQUENCE SEQ_AUTHOR_UID
START WITH 1
INCREMENT BY 1
order;
select SEQ_AUTHOR_UID.nextval;