Information from sequences inside CREATE TABLE statements is extracted in order to build a CREATE OR REPLACE SEQUENCE statement in the migrated Snowflake code.
This EWI is added to the original location of the sequence from which the information was originally collected to create that statement.
CREATE SET TABLE T_2031,FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,CHECKSUM = DEFAULT,DEFAULT MERGEBLOCKRATIO(SEQ1_2031 INTEGER FORMAT '--,---,---,--9' NOT NULL GENERATED ALWAYS AS IDENTITY,SEQ2_2031 INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1INCREMENT BY 1MINVALUE -2147483647MAXVALUE 2147483647NO CYCLE),SEQ3_2031 GENERATED ALWAYS AS IDENTITY (NO CYCLE));
CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ1_2031COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ1_2031';CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ2_2031START WITH 1INCREMENT BY 1COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ2_2031';CREATE OR REPLACE SEQUENCE PUBLIC.T_2031_SEQ3_2031COMMENT = 'FOR TABLE-COLUMN PUBLIC.T_2031.SEQ3_2031';/*** MSC-WARNING - MSCEWI2015 - SET TABLE FUNCTIONALITY NOT SUPPORTED ***/CREATE TABLE PUBLIC.T_2031 (SEQ1_2031 INTEGER DEFAULT PUBLIC.T_2031_SEQ1_2031.NEXTVAL NOT NULL /*** MSC-WARNING - MSCEWI2040 - FORMAT '--,---,---,--9' NOT SUPPORTED ***/ /*** MSC-WARNING - MSCEWI2031 - SEQUENCE - GENERATED ALWAYS ***/ ,SEQ2_2031 INTEGER DEFAULT PUBLIC.T_2031_SEQ2_2031.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED ALWAYS START WITH 1 INCREMENT BY 1 MINVALUE -2147483647 MAXVALUE 2147483647 NO CYCLE ***/ ,SEQ3_2031 DEFAULT PUBLIC.T_2031_SEQ3_2031.NEXTVAL /*** MSC-WARNING - MSCEWI2031 - SEQUENCE - GENERATED ALWAYS NO CYCLE ***/);
CREATE TABLE new_employees(id_num int IDENTITY(1,1),fname varchar (20),minit char(1),lname varchar(30));
CREATE OR REPLACE SEQUENCE PUBLIC.new_employees_id_numSTART WITH 1INCREMENT BY 1COMMENT = 'FOR TABLE-COLUMN PUBLIC.new_employees.id_num';CREATE OR REPLACE TABLE PUBLIC.new_employees (id_num INT DEFAULT PUBLIC.new_employees_id_num.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE - GENERATED BY DEFAULT START 1 INCREMENT 1 ***/ ,fname VARCHAR (20),minit CHAR(1),lname VARCHAR(30));
Snowflake has an IDENTITY datatype. SnowConvert does not transform IDENTITY columns to this datatype because there is now way to update the sequence created by the identity definition. After data is migrated, it might be necessary to update the
START WITH value to ensure that old values are not reused that can be already on the source data. In general the sequence is considered more flexible as identity creates an unalterable sequence behind the scene.
Also notice that this will affect the database metadata. If a column uses the IDENTITY then the INFORMATION_SCHEMA.COLUMNS reports that columns as IS_IDENTITY when the sequence is used that column will not be reported as such but you can check the sequence usage in COLUMN_DEFAULT.
No additional user actions are required.