Skip to main content

Snowflake Sequences and Autoincrement/Identity

SqlDBM Support
Updated

Sequences are used to generate unique numbers across sessions and statements (including concurrent statements). They can be used to generate values for a primary key or any column that requires a unique value.

Note that an alternative to declaring a standalone sequence object is to set the AUTOINCREMENT / IDENTITY property on a column (these are synonymous). In this case, Snowflake will transparently create and manage an associated sequence.

Both are valid options for generating unique numbers and are covered below. Remember that Sequences and Identity are only applicable to numeric (e.g., integer, number, float, etc.) data types.

Option 1 - Identity

To set the identity property, select a numeric column from the table display on a diagram or by double-clicking on the table to bring up the column editor.

This will bring up the column properties pane on the right of the screen.

Expand the options menu, enable the "Identity" checkbox, and specify the start and increment values.

Increment properties:

  • Start: number at which the sequence should start (typically, 1)

  • Increment: the gap between generated values (typically, 1)

Option 2 - Sequence

Use this option to create a dedicated sequence object that can be shared among multiple tables and manipulated independently of the column/table to which it is assigned.

Create Sequence

From the Database Explorer screen, navigate to "Sequences" and select "Create New" from the menu.

Set the Sequence name and properties on the right-screen pane.

Sequence Properties:

  • Schema: the schema in which the sequence should be created (can be blank)

  • Name: unique object name

  • Starts with: number at which the sequence should start (typically, 1)

  • Increment by: the gap between generated values (typically, 1)

  • Description: object-level comment for the sequence

Alternatively, sequences can be created directly from the column properties described above.

Expand the options menu, select the Sequence dropdown, click "+ Add New," and follow the previously described steps.

Associate a Sequence with a column

Once a sequence has been created, it can now be assigned to one or multiple columns.

Select a numeric column from the table display on a diagram or by double-clicking on the table to bring up the column editor.

This will bring up the column properties pane on the right of the screen.

Expand the options menu and select the created sequence from the Sequence dropdown.

See also:

Related articles