Identity Column Specification Identity column specification option
Grammar Syntax
Copy <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
Copy CREATE TABLE TABLE_COLUMN_OPTION (
COLUMN1 INTEGER GENERATED ALWAYS AS IDENTITY
);
Snowflake Output Code
Copy 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)
Copy CREATE TABLE Example1 (
RECORD_KEY DECIMAL ( 10 , 0 ) GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1 )
);
Snowflake Output Code
Copy 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)
Copy CREATE TABLE Example2 (
RECORD_KEY DECIMAL ( 10 , 0 ) GENERATED ALWAYS AS IDENTITY (MAXVALUE 1000 )
);
Snowflake Output Code
Copy 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 )
Copy CREATE TABLE Example3 (
RECORD_KEY DECIMAL ( 10 , 0 ) GENERATED ALWAYS AS IDENTITY (MINVALUE 1000 )
);
Snowflake Output Code
Copy 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)
Copy CREATE TABLE Example4 (
RECORD_KEY DECIMAL ( 10 , 0 ) GENERATED ALWAYS AS IDENTITY ( NO CYCLE)
);
Snowflake Output Code
Copy 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 )
Copy CREATE TABLE Example5 (
RECORD_KEY DECIMAL ( 10 , 0 ) GENERATED ALWAYS AS IDENTITY ( START WITH 1 )
);
Snowflake Output Code
Copy 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.
Copy CREATE TABLE dbo .Employees (
sysstart TIMESTAMP ( 6 ) GENERATED ALWAYS AS ROW START ,
sysstart2 TIMESTAMP ( 6 ) GENERATED ALWAYS AS ROW END
);
Snowflake Output Code
Copy 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