Tables

Some parts in the output code are omitted for clarity reasons.

Basic Create Table

Source

IN -> SqlServer_01.sql

CREATE TABLE [MYSCHEMA].[MYTABLE]
(
    [COL1] INT IDENTITY (1,1) NOT NULL,
    [COL2] INT,
    [COL2 COL3 COL4] VARCHAR,
    [COL VARCHAR_SPANISH] [VARCHAR](20) COLLATE Modern_Spanish_CI_AI DEFAULT 'HOLA',
    [COL VARCHAR_LATIN] [VARCHAR](20) COLLATE Latin1_General_CI_AI DEFAULT 'HELLO' 
);

Expected

OUT -> SqlServer_01.sql

CREATE OR REPLACE SEQUENCE MYSCHEMA.MYTABLE_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN MYSCHEMA.MYTABLE.COL1';

CREATE OR REPLACE TABLE MYSCHEMA.MYTABLE (
    COL1 INT DEFAULT MYSCHEMA.MYTABLE_COL1.NEXTVAL NOT NULL,
    COL2 INT,
    "COL2 COL3 COL4" VARCHAR,
    "COL VARCHAR_SPANISH" VARCHAR(20) COLLATE 'ES-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HOLA',
    "COL VARCHAR_LATIN" VARCHAR(20) COLLATE 'EN-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HELLO'
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

Temporary Tables

In the source code, there can be some table names that start with the character #.

IN -> SqlServer_02.sql

CREATE TABLE #MyLocalTempTable (
        COL1 INT,
        COL2 INT
);

If that is the case, they are transformed into temporary tables in the output code.

Let's see how the code from above would be migrated.

OUT -> SqlServer_02.sql

CREATE OR REPLACE TEMPORARY TABLE T_MyLocalTempTable (
        COL1 INT,
        COL2 INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

As you can see, TEMPORARY was added to the definition of the table, and the character # was replaced with T_.

Also, all references of the table will be transformed too, to match the new name given to the temporary table.

NULL and NOT NULL Column Option

NULL and NOT NULL column options are supported in Snowflake.

Source

IN -> SqlServer_03.sql

CREATE TABLE [SCHEMA1].[TABLE1](
	[COL1] [varchar](20) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [SCHEMA1].[TABLE2](
	[COL1] [varchar](20) NULL
) ON [PRIMARY]
GO

Expected

OUT -> SqlServer_03.sql

CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
	COL1 VARCHAR(20) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

CREATE OR REPLACE TABLE SCHEMA1.TABLE2 (
	COL1 VARCHAR(20) NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

Identity Column Option

For identity columns, a sequence is created and assigned to the column.

Source

IN -> SqlServer_04.sql

CREATE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT IDENTITY (666, 313) NOT NULL
);

Expected

OUT -> SqlServer_04.sql

CREATE OR REPLACE SEQUENCE acct3.UnidentifiedCash3_UnidentifiedCash_ID3
START WITH 666
INCREMENT BY 313
COMMENT = 'FOR TABLE-COLUMN acct3.UnidentifiedCash3.UnidentifiedCash_ID3';

CREATE OR REPLACE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT DEFAULT acct3.UnidentifiedCash3_UnidentifiedCash_ID3.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

For more information, you can also check this warning related to this transformation.

Default Column Option

The default Expr is supported in Snowflake, however, in SqlServer it can come together with a constraint Name. Since that part is not supported in Snowflake, it is removed and a warning is added.

Source

IN -> SqlServer_05.sql

CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] VARCHAR (10) CONSTRAINT [constraintName] DEFAULT ('0') NOT NULL
);

Expected

OUT -> SqlServer_05.sql

CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(10)
                     --** SSC-FDM-0012 - CONSTRAINT IN DEFAULT EXPRESSION IS NOT SUPPORTED IN SNOWFLAKE **
                     DEFAULT ('0') NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

Column Constraint

Source

IN -> SqlServer_06.sql

CREATE TABLE [SalesLT].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) NOT NULL,
	[AddressLine2] [nvarchar](60) NULL,
	[City] [nvarchar](30) NOT NULL,