Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

OptionDescription
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 CYCLEDetermines 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.