Sequences and auto-increment in Snowflake - Step-by-Step Execution

Process Flow - Sequences and auto-increment

Create Sequence

↓

Sequence Initialized

↓

Request Next Value

↓

Sequence Returns Current Value

↓

Sequence Increments

↓

Use Value in Table Insert

↓

Repeat Request for Next Value

This flow shows how a sequence is created, then each time a next value is requested, the sequence returns the current number and increments for the next call.

Execution Sample

Snowflake

CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1;
SELECT my_seq.nextval;
INSERT INTO my_table (id, name) VALUES (my_seq.nextval, 'Alice');
SELECT my_seq.nextval;

This code creates a sequence starting at 1, gets the next value twice, and inserts a row using the sequence value.

Process Table

Step	Action	Sequence Current Value	Returned Value	Notes
1	Create sequence my_seq START WITH 1 INCREMENT BY 1	0 (initialized)	N/A	Sequence created but no value generated yet
2	SELECT my_seq.nextval	1	1	First call returns 1 and increments sequence to 1
3	INSERT INTO my_table (id, name) VALUES (my_seq.nextval, 'Alice')	2	2	Nextval returns 2 for insert, sequence increments to 2
4	SELECT my_seq.nextval	3	3	Nextval returns 3, sequence increments to 3
5	No more calls	3	N/A	Sequence holds last value 3, no further increments

💡 Execution stops after no more nextval calls; sequence holds last incremented value.

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
my_seq.current_value	0	1	2	3	3
Returned nextval	N/A	1	2	3	N/A

Key Moments - 3 Insights

Why does the sequence start at 0 but the first nextval returns 1?

The sequence is initialized at 0 internally but the first nextval call increments it to the start value 1 before returning, as shown in execution_table step 2.

Does calling nextval change the sequence value even if not used in insert?

Yes, each nextval call increments the sequence regardless of usage, as seen in steps 2, 3, and 4 where current_value increments each time.

What happens if you call nextval multiple times without inserting?

Each call increments and returns the next number, so the sequence value advances even without inserts, shown by the increments in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the sequence current value after step 3?

A 2

B 1

C 3

D 0

💡 Hint

Check the 'Sequence Current Value' column for step 3 in execution_table.

At which step does the sequence return the value 3?

A Step 2

B Step 3

C Step 4

D Step 1

💡 Hint

Look at the 'Returned Value' column in execution_table to find when 3 is returned.

If you call nextval twice more after step 4, what will be the returned values?

A 5 and 6

B 4 and 5

C 3 and 4

D 2 and 3

💡 Hint

Sequence increments by 1 each nextval call; after 3, next values are 4 then 5.

Concept Snapshot

CREATE SEQUENCE name START WITH x INCREMENT BY y;
Use name.nextval to get next number.
Each nextval call returns current value and increments.
Useful for unique IDs in tables.
Sequence state persists until dropped.

Full Transcript

This visual execution shows how Snowflake sequences work. First, a sequence is created with a start and increment value. Initially, the sequence holds no value. When nextval is called, it returns the current sequence number and increments it for the next call. This number can be used in inserts to provide unique IDs. Each call to nextval advances the sequence, even if the value is not used. The sequence keeps track of its current value until the sequence is dropped or reset.