Snowflake Sequence - How to Create and Use it?

A Sequence is a named object in an individual Snowflake schema and database. These sequences are used to generate unique numbers. You can use sequence to generate unique numbers that can be used as surrogate key values for primary key values.

Snowflake Sequence Overview

A sequence value can represent a 64-bit two’s complement integer. You can use it wherever you would use numeric values. Snowflake supports user sequences for the four integer types: byteint, smallint, integer, and bigint. For example, you can use sequences in unique columns, primary key columns, etc.

You can even create a sequence with an initial value, an increment value. The Snowflake sequences will throw an error when it reaches the threshold.

When you generate the sequence number, the cloud data warehouse system increments the sequence independent of the transaction commit or rollback. Therefore, rollback doesn’t return the value back to sequence.

Snowflake CREATE or ALTER Sequence Syntax

Following syntax is used to create or alter sequences in Snowflake.

You can use CREATE or REPLACE SEQUENCE command to create or alter the sequence.

CREATE [ OR REPLACE ] SEQUENCE [ IF NOT EXISTS ] <name>
  [ WITH ]
  [ START [ WITH ] [ = ] <initial_value> ]
  [ INCREMENT [ BY ] [ = ] <sequence_interval> ]
  [ COMMENT = '<comments if any>' ]

Meanings and values of options:

[ START [ WITH ] [ = ] ] initial_value – Specifies the first value returned by the sequence.
INCREMENT [ BY ] [ = ] sequence_interval – Specifies the step interval of the sequence

Snowflake Create Sequence Example

You use CREATE SEQUENCE statement to create sequence.

For example, consider below command to create sequence which produces positive integer values.

CREATE SEQUENCE sequence1 
START WITH 1 
INCREMENT BY 1 
COMMENT = 'Positive Sequence';

Getting Values from Snowflake Sequences

The sequences may be accessed in queries as expressions of the form seq_name.NEXTVAL. Each occurrence of a sequence generates a set of distinct values. However, multiple use of seq_name.NEXTVAL in a single SELECT statement will not provide single value, but, multiple unique values.

For example, consider below SQL statement to get value from sequence1.

SELECT sequence1.NEXTVAL;

+---------+
| NEXTVAL |
|---------|
|       1 |
+---------+

Snowflake Sequences as Table Functions

The Snowflake provides a spacial table function, GETNEXTVAL, allows you to generate sequence values.

The GETNEXTVAL is a special 1-row table function that generates a unique value to other objects in the SELECT statement. A call to GETNEXTVAL must be aliased, otherwise the generated values cannot be referenced. Multiple columns may refer to a generated value by accessing this alias. The GETNEXTVAL alias contains an attribute also named NEXTVAL.

For example, consider below example that uses GETNEXTVAL table function.

create or replace table seq_test (n number);

insert into seq_test values (10), (20), (30);

select n, s.nextval from seq_test, table(getnextval(sequence1)) s;

+----+---------+
|  N | NEXTVAL |
|----+---------|
| 10 |       2 |
| 20 |       3 |
| 30 |       4 |
+----+---------+

Alter Snowflake Sequence

You can use REPLACE SEQUENCE command to alter sequences.

For example, consider below SQL statement to alter sequence1 to reset initial value.

CREATE or REPLACE SEQUENCE sequence1 
START WITH 1 
INCREMENT BY 1 
COMMENT = 'Positive Sequence';

Dropping a Snowflake Sequence

You can use the DROP SEQUENCE schema to remove sequence from the schema or database.

For example, consider following drop sequence statement.

DROP SEQUENCE sequence1;

+---------------------------------+
| status                          |
|---------------------------------|
| SEQUENCE1 successfully dropped. |
+---------------------------------+

Known problem with Snowflake sequences

Sequence always provides a unique value. However, be advised that, you are not guaranteed that sequence numbers will be predictable, monotonically increasing values.