Sequences and auto-increment in Snowflake - Commands & Configuration

Introduction

When you want to create unique numbers automatically for each new row in a table, sequences help by generating these numbers one after another without repeats.

When you need a unique ID for each new record in a table without manually entering it.

When multiple users insert data at the same time and you want to avoid duplicate IDs.

When you want to control the starting number and increment step of IDs.

When you want to reuse a sequence number in different tables consistently.

When you want to generate numbers outside of table inserts for other purposes.

Commands

This command creates a new sequence named 'my_sequence' that starts at 1 and increases by 1 each time it is used.

Terminal

CREATE SEQUENCE my_sequence START = 1 INCREMENT = 1;

Expected Output

Sequence MY_SEQUENCE successfully created.

This command gets the next number from the sequence, which will be 1 the first time it runs.

Terminal

SELECT my_sequence.NEXTVAL;

Expected Output

NEXTVAL

1

This creates a table 'users' where the 'id' column automatically gets the next sequence number when a new row is added.

Terminal

CREATE TABLE users (id NUMBER DEFAULT my_sequence.NEXTVAL, name STRING);

Expected Output

Table USERS successfully created.

This inserts two new users. Their 'id' values will be automatically assigned from the sequence.

Terminal

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

Expected Output

2 rows inserted.

This shows all rows in the 'users' table with their auto-incremented IDs.

Terminal

SELECT * FROM users;

Expected Output

ID | NAME

1  | Alice

2  | Bob

Key Concept

If you remember nothing else from this pattern, remember: sequences generate unique numbers automatically and safely for new data entries.

Common Mistakes

Not using NEXTVAL to get the next sequence number and trying to insert sequence name directly.

The sequence name alone does not produce a number; NEXTVAL must be called to get the next value.

Always use sequence_name.NEXTVAL to get the next number from the sequence.

Creating a sequence but not linking it as a default value in the table column.

Without linking, the sequence won't auto-assign IDs during inserts.

Set the column default to sequence_name.NEXTVAL when creating or altering the table.

Assuming sequence numbers reset automatically after reaching a max value.

Sequences keep increasing unless explicitly altered or dropped.

Manage sequence limits manually if needed, or recreate the sequence to reset.

Summary

Create a sequence to generate unique numbers starting from a chosen value.

Use sequence_name.NEXTVAL to get the next number from the sequence.

Set a table column default to the sequence's NEXTVAL for automatic numbering on inserts.

Insert rows without specifying the ID; the sequence assigns it automatically.

Query the table to see the auto-incremented IDs assigned.