Sequences and columns: Changes to SHOW command, view, and GET_DDL function output

Attention

This behavior change is in the 2023_06 bundle.

For the current status of the bundle, refer to Bundle History.

In the current Snowflake release, Snowflake has introduced a new ORDER and NOORDER parameter for sequences and table columns:

  • ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order.

  • NOORDER specifies that the values are not guaranteed to be in increasing order.

These new parameters appear in the output of the commands, functions, and views.

The output of the following commands and views includes this new ordered column:

Column Name

Data Type

Description

ordered

TEXT

Specifies whether or not the values are generated in increasing order.

  • For the SHOW SEQUENCES and DESCRIBE SEQUENCE commands, the column contains:

    • Y (if the sequence has the ORDER parameter)

    • N (if the sequence has the NOORDER parameter).

  • For the Information Schema and Account Usage SEQUENCES views, the column contains:

    • YES (if the sequence has the ORDER parameter)

    • NO (if the sequence has the NOORDER parameter).

In the output of the SHOW COLUMNS command, the autoincrement column includes the ORDER or NOORDER parameter:

Previously:

If the column auto-increments by 1 with the starting value of 1, the autoincrement column contains:

start 1 increment 1
Currently:

If the column has the ORDER parameter set, the autoincrement column contains:

start 1 increment 1 order

If the column has the NOORDER parameter set, the autoincrement column contains:

start 1 increment 1 noorder

The output of the Information Schema and Account Usage COLUMNS views includes a new identity_ordered column:

Column Name

Data Type

Description

identity_ordered

TEXT

Specifies whether or not this column is an identity column with generated values in increasing order.

  • If the column is an identity column and has the ORDER parameter, the column contains YES.

  • If the column is an identity column and has the NOORDER parameter, the column contains NO.

Finally, the output of the GET_DDL function will include the ORDER and NOORDER parameter for sequences and columns.

Currently:

If the column auto-increments by 1 with the starting value of 1, the output of the GET_DDL function does not include the ORDER or NOORDER parameters:

create or replace table MYTABLE(
  MYCOL ... start 1 increment 1
  ...
Pending:

If the column has the ORDER parameter set, the GET_DDL output includes the ORDER parameter:

create or replace table MYTABLE(
  MYCOL ... start 1 increment 1 order
  ...

If the column has the NOORDER parameter set, the GET_DDL output includes the NOORDER parameter:

create or replace table MYTABLE(
  MYCOL ... start 1 increment 1 noorder
  ...

Ref: 1225

Language: English