How to Create Sequence in Snowflake: Syntax and Example
In Snowflake, you create a sequence using the
CREATE SEQUENCE statement, which generates unique numeric values. You can specify options like start value and increment to control the sequence behavior.Syntax
The CREATE SEQUENCE statement defines a new sequence object. You can set options like START WITH to set the first number, INCREMENT BY to define the step size, and MINVALUE or MAXVALUE to limit the range.
sql
CREATE SEQUENCE sequence_name [ START WITH <number> ] [ INCREMENT BY <number> ] [ MINVALUE <number> | NO MINVALUE ] [ MAXVALUE <number> | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE <number> | NO CACHE ];
Example
This example creates a sequence named order_seq that starts at 1000 and increments by 10 each time it is used.
sql
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 10 NO CYCLE CACHE 5;
Output
Sequence ORDER_SEQ created.
Common Pitfalls
Common mistakes include forgetting to specify NO CYCLE if you don't want the sequence to restart, or not using the sequence correctly in queries. Also, caching too many values can cause gaps if the system restarts.
sql
/* Wrong: Sequence does NOT cycle by default, so this is correct behavior */ CREATE SEQUENCE seq_cycle START WITH 1 INCREMENT BY 1; /* Right: Explicitly specify NO CYCLE to avoid confusion */ CREATE SEQUENCE seq_no_cycle START WITH 1 INCREMENT BY 1 NO CYCLE;
Quick Reference
| Option | Description |
|---|---|
| START WITH | Sets the first value of the sequence. |
| INCREMENT BY | Sets how much the sequence increases each time. |
| MINVALUE | Sets the minimum value the sequence can generate. |
| MAXVALUE | Sets the maximum value the sequence can generate. |
| CYCLE / NO CYCLE | Determines if the sequence restarts after reaching max/min. |
| CACHE | Preallocates sequence numbers for faster access. |
Key Takeaways
Use CREATE SEQUENCE to generate unique numbers in Snowflake.
Specify START WITH and INCREMENT BY to control sequence values.
Use NO CYCLE to prevent the sequence from restarting automatically.
Be mindful of CACHE size to avoid gaps in sequence numbers.
Sequences are useful for generating IDs or unique keys.
