Identity Column Specification

Identity column specification option

Grammar Syntax

<identity column specification> ::=
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
      [ <left paren>  <common sequence generator options>  <right paren>  ]
<generation clause> ::=
  <generation rule>  AS <generation expression> 
<generation rule> ::=
  GENERATED ALWAYS
<generation expression> ::=
  <left paren>  <value expression>  <right paren> 
  
<common sequence generator options> ::=
  <common sequence generator option> ...
<common sequence generator option> ::=
    <sequence generator start with option> 
  | <basic sequence generator option> 
<basic sequence generator option> ::=
    <sequence generator increment by option> 
  | <sequence generator maxvalue option> 
  | <sequence generator minvalue option> 
  | <sequence generator cycle option> 
<sequence generator data type option> ::=
  AS <data type> 
<sequence generator start with option> ::=
  START WITH <sequence generator start value> 
<sequence generator start value> ::=
  <signed numeric literal> 
<sequence generator increment by option> ::=
  INCREMENT BY <sequence generator increment> 
<sequence generator increment> ::=
  <signed numeric literal> 
<sequence generator maxvalue option> ::=
    MAXVALUE <sequence generator max value> 
  | NO MAXVALUE
<sequence generator max value> ::=
  <signed numeric literal> 
<sequence generator minvalue option> ::=
    MINVALUE <sequence generator min value> 
  | NO MINVALUE
<sequence generator min value> ::=
  <signed numeric literal> 
<sequence generator cycle option> ::=
    CYCLE
  | NO CYCLE

Click here to go to the ANSI SQL Standard specification for this syntax.

This clause is partially supported in Snowflake

Sample Source Patterns

Sample with GENERATED ALWAYS AS IDENTITY

CREATE TABLE TABLE_COLUMN_OPTION(
    COLUMN1 INTEGER GENERATED ALWAYS AS IDENTITY
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE TABLE_COLUMN_OPTION_COLUMN1
COMMENT = 'FOR TABLE-COLUMN TABLE_COLUMN_OPTION.COLUMN1';
CREATE OR REPLACE TABLE TABLE_COLUMN_OPTION (
    COLUMN1 INTEGER DEFAULT TABLE_COLUMN_OPTION_COLUMN1.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   ***/
);

Sample with GENERATED ALWAYS AS IDENTITY(INCREMENT BY)

CREATE TABLE Example1 (
    RECORD_KEY DECIMAL(10, 0) GENERATED ALWAYS AS IDENTITY(INCREMENT BY 1)
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE Example1_RECORD_KEY
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN Example1.RECORD_KEY';
CREATE OR REPLACE TABLE Example1 (
    RECORD_KEY DECIMAL(10, 0) DEFAULT Example1_RECORD_KEY.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   INCREMENT BY 1 ***/
);

Sample with GENERATED ALWAYS AS IDENTITY(MAXVALUE)

CREATE TABLE Example2 (
    RECORD_KEY DECIMAL(10, 0) GENERATED ALWAYS AS IDENTITY(MAXVALUE 1000)
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE Example2_RECORD_KEY
COMMENT = 'FOR TABLE-COLUMN Example2.RECORD_KEY';
CREATE OR REPLACE TABLE Example2 (
    RECORD_KEY DECIMAL(10, 0) DEFAULT Example2_RECORD_KEY.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   MAXVALUE 1000 ***/
);

Sample with GENERATED ALWAYS AS IDENTITY(MINVALUE )

CREATE TABLE Example3 (
    RECORD_KEY DECIMAL(10, 0) GENERATED ALWAYS AS IDENTITY(MINVALUE 1000)
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE Example3_RECORD_KEY
COMMENT = 'FOR TABLE-COLUMN Example3.RECORD_KEY';
CREATE OR REPLACE TABLE Example3 (
    RECORD_KEY DECIMAL(10, 0) DEFAULT Example3_RECORD_KEY.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   MINVALUE 1000 ***/
);

Sample with GENERATED ALWAYS AS IDENTITY(NO CYCLE)

CREATE TABLE Example4 (
    RECORD_KEY DECIMAL(10, 0) GENERATED ALWAYS AS IDENTITY(NO CYCLE)
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE Example4_RECORD_KEY
COMMENT = 'FOR TABLE-COLUMN Example4.RECORD_KEY';
CREATE OR REPLACE TABLE Example4 (
    RECORD_KEY DECIMAL(10, 0) DEFAULT Example4_RECORD_KEY.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   NO CYCLE ***/
);

Sample with GENERATED ALWAYS AS IDENTITY(START WITH )

CREATE TABLE Example5 (
    RECORD_KEY DECIMAL(10, 0) GENERATED ALWAYS AS IDENTITY(START WITH 1)
);

Snowflake Output Code

CREATE OR REPLACE SEQUENCE Example5_RECORD_KEY
    START WITH 1
COMMENT = 'FOR TABLE-COLUMN Example5.RECORD_KEY';
CREATE OR REPLACE TABLE Example5 (
    RECORD_KEY DECIMAL(10, 0) DEFAULT Example5_RECORD_KEY.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS   START WITH 1 ***/
);

Sample with GENERATED ALWAYS AS ROW START END

AS ROW START and AS ROW END Options are not supported.

CREATE TABLE dbo.Employees (
    sysstart TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    sysstart2 TIMESTAMP(6) GENERATED ALWAYS AS ROW END
);

Snowflake Output Code

CREATE TABLE dbo.Employees (
    sysstart TIMESTAMP(6)
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--                          GENERATED ALWAYS AS ROW START
                                                       ,
    sysstart2 TIMESTAMP(6)
-- ** MSC-ERROR - MSCEWI1021 - THE FOLLOWING NODE IS NOT SUPPORTED **
--                           GENERATED ALWAYS AS ROW END
);

Related EWIs

1. MSCEWI1021: Node not supported.

2. MSCEWI1048: Sequence Warning.