Categories:

Table, View, & Sequence DDL

# CREATE SEQUENCE¶

Creates a new sequence, which can be used for generating sequential, unique numbers.

Important

Snowflake does not guarantee generating sequence numbers without gaps. The generated numbers consistently increase in value (or decrease in value if the step size is negative) but are not necessarily contiguous.

For more details, see Using Sequences.

## Syntax¶

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


## Required Parameters¶

name

Specifies the identifier for the sequence; must be unique for the schema in which the sequence is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details about identifiers, see Identifier Requirements.

## Optional Parameters¶

START [ WITH ] [ = ] initial_value

Specifies the first value returned by the sequence. Supported values are any value that can be represented by a 64-bit two’s complement integer (from -2^63 to 2^63 - 1).

Default: 1

INCREMENT [ BY ] [ = ] sequence_interval

Specifies the step interval of the sequence:

• For positive sequence interval n, the next n-1 values are reserved by each sequence call.

• For negative sequence interval -n, the next n-1 lower values are reserved by each sequence call.

Supported values are any non-zero value that can be represented by a 64-bit two’s complement integer.

Default: 1

COMMENT = 'string_literal'

Specifies a comment for the sequence.

Default: No value

## Access Control Requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE SEQUENCE

Schema

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Access Control in Snowflake.

## Usage Notes¶

• The first/initial value for a sequence cannot be changed after the sequence is created.

• A sequence does not necessarily produce a gap-free sequence. Values increase (until the limit is reached) and are unique, but are not necessarily contiguous. For more information, including the upper and lower limits, see Sequence Semantics.

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

• CREATE OR REPLACE <object> statements are atomic. That is, when the object is replaced, the old object deletion and the new object creation are processed in a single transaction.

## Examples¶

Here is a simple example of using sequences:

CREATE OR REPLACE SEQUENCE seq_01 START = 1 INCREMENT = 1;
CREATE OR REPLACE TABLE sequence_test_table (i INTEGER);

SELECT seq_01.nextval;
+---------+
| NEXTVAL |
|---------|
|       1 |
+---------+


Run the same query again; note how the sequence numbers change:

SELECT seq_01.nextval;
+---------+
| NEXTVAL |
|---------|
|       2 |
+---------+


Now use the sequence while inserting into a table:

INSERT INTO sequence_test_table (i) VALUES (seq_01.nextval);

SELECT i FROM sequence_test_table;
+---+
| I |
|---|
| 3 |
+---+


Create a sequence that increments by 5 rather than by 1:

CREATE OR REPLACE SEQUENCE seq_5 START = 1 INCREMENT = 5;

SELECT seq_5.nextval a, seq_5.nextval b, seq_5.nextval c, seq_5.nextval d;
+---+---+----+----+
| A | B |  C |  D |
|---+---+----+----|
| 1 | 6 | 11 | 16 |
+---+---+----+----+


Run the same query again; note how the sequence numbers change. You might expect that the next set of sequence numbers would start 5 higher than the previous statement left off. However, the next sequence number starts 20 higher (5 * 4, where 5 is the size of the increment and 4 is the number of NEXTVAL operations in the statement):

SELECT seq_5.nextval a, seq_5.nextval b, seq_5.nextval c, seq_5.nextval d;
+----+----+----+----+
|  A |  B |  C |  D |
|----+----+----+----|
| 36 | 41 | 46 | 51 |
+----+----+----+----+


This example demonstrates that you can use a sequence as a default value for a column to provide unique identifiers for each row in a table:

CREATE OR REPLACE SEQUENCE seq90;
CREATE OR REPLACE TABLE sequence_demo (i INTEGER DEFAULT seq90.nextval, dummy SMALLINT);
INSERT INTO sequence_demo (dummy) VALUES (0);

-- Keep doubling the number of rows:
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;
INSERT INTO sequence_demo (dummy) SELECT dummy FROM sequence_demo;

SELECT i FROM sequence_demo ORDER BY i LIMIT 10;
+----+
|  I |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+


This query shows that each row in the table has a distinct value:

SELECT COUNT(i), COUNT(DISTINCT i) FROM sequence_demo;
+----------+-------------------+
| COUNT(I) | COUNT(DISTINCT I) |
|----------+-------------------|
|     1024 |              1024 |
+----------+-------------------+


More examples are available in Using Sequences.