Tables

Basic Create Table

Source

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

CREATE TABLE "MYSCHEMA"."MYTABLE" (
"COL1" INT DEFAULT MYSCHEMA.MYTABLE_COL1.NEXTVAL /*** MSC-WARNING - MSCEWI2031 - SEQUENCE -  GENERATED BY DEFAULT  START 1 INCREMENT 1 ***/ NOT NULL,
"COL2" INT,
"COL2 COL3 COL4" VARCHAR,
"COL VARCHAR_SPANISH" VARCHAR(20) COLLATE 'ES-CI-AI' DEFAULT 'HOLA',
"COL VARCHAR_LATIN" VARCHAR(20) COLLATE 'EN-CI-AI' DEFAULT 'HELLO'
);

Temporary Tables

In the source cody there can be some tables names that start with the character #.

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

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

Le'ts see how the code from above would be migrated.

CREATE TEMPORARY TABLE PUBLIC.T_MyLocalTempTable (
COL1 INT,
COL2 INT
);

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

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

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

Expected

CREATE TABLE "SCHEMA1"."TABLE1" (
"COL1" VARCHAR(20) NOT NULL
);

CREATE TABLE "SCHEMA1"."TABLE2" (
"COL1" VARCHAR(20) NULL
);

Identity Column Option

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

Source

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

Expected

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

CREATE TABLE MYDB.acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT DEFAULT MYDB.acct3.UnidentifiedCash3_UnidentifiedCash_ID3.NEXTVAL /*** MSC-WARNING - MSCEWI2031 - SEQUENCE -  GENERATED BY DEFAULT  START 666 INCREMENT 313 ***/ NOT NULL
);

,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

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

Expected

CREATE TABLE "SCHEMA1"."TABLE1" (
"COL1" VARCHAR (10) /*** MSC-WARNING - MSCEWI1097 - CONSTRAINT IN DEFAULT EXPRESSION NOT SUPPORTED ***/
 DEFAULT ('0') NOT NULL
);

Column Constraint

Source

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,
	[StateProvince] [dbo].[Name] NOT NULL,
	[CountryRegion] [dbo].[Name] NOT NULL,
	[PostalCode] [nvarchar](15) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
	(
		[AddressID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
	CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED 
	(
		[rowguid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Expected

CREATE OR REPLACE SEQUENCE SalesLT.Address_AddressID
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN SalesLT.Address.AddressID';

CREATE OR REPLACE TABLE SalesLT.Address (
AddressID INT DEFAULT SalesLT.Address_AddressID.NEXTVAL /*** MSC-WARNING - MSCEWI1048 - SEQUENCE -  GENERATED BY DEFAULT  START 1 INCREMENT 1 ***/
-- ** MSC-WARNING - MSCEWI1042 - Commented NOT FOR REPLICATION - THIS IS NON-RELEVANT **
--                                                                                                                                                   NOT FOR REPLICATION
                                                                                                                                                                       NOT NULL,
AddressLine1 VARCHAR(60) NOT NULL,
AddressLine2 VARCHAR(60) NULL,
City VARCHAR(30) NOT NULL,
StateProvince dbo.Name /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR DBO.NAME DATA TYPE IS PLANNED TO BE DELIVERED IN THE FUTURE ***/ NOT NULL,
CountryRegion dbo.Name /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR DBO.NAME DATA TYPE IS PLANNED TO BE DELIVERED IN THE FUTURE ***/ NOT NULL,
PostalCode VARCHAR(15) NOT NULL,
rowguid VARCHAR
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--                ROWGUIDCOL
                            NOT NULL,
ModifiedDate DATETIME NOT NULL,
CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID /*** MSC-INFORMATION - MSCINF0019 - Column sorting is not supported in Snowflake ***/)
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH INDEX - THIS IS NON-RELEVANT **
--                                                                                                                                          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
                                                                                                                                                                                                                                                                                                    
-- ** MSC-WARNING - MSCEWI1042 - Commented ON PRIMARY - THIS IS NON-RELEVANT **
--                                                                                                                                                                                                                                                                                                     ON PRIMARY
                                                                                                                                                                                                                                                                                                               ,
CONSTRAINT AK_Address_rowguid UNIQUE (rowguid /*** MSC-INFORMATION - MSCINF0019 - Column sorting is not supported in Snowflake ***/)
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH INDEX - THIS IS NON-RELEVANT **
--                                                                                                                                 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
                                                                                                                                                                                                                                                                                           
-- ** MSC-WARNING - MSCEWI1042 - Commented ON PRIMARY - THIS IS NON-RELEVANT **
--                                                                                                                                                                                                                                                                                            ON PRIMARY
);

Collate Column Option

For the transformation of Collate, please check the following link.

ENCRYPTED WITH Column Option

The Encrypted With is not supported in Snowflake, so it is being removed and a warning is added.

Source

CREATE TABLE [SCHEMA1].[TABLE1] (
    [COL1] NVARCHAR(60) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Expected

CREATE TABLE "SCHEMA1"."TABLE1" (
"COL1" NVARCHAR(60)
-- ** MSC-WARNING - MSCEWI4003 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
--                    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

NOT FOR REPLICATION

The NOT FOR REPLICATION option is not supported in Snowflake. It is used for the identity that is being migrated to a SEQUENCE.

Source

CREATE TABLE [TABLE1] (
    [COL1] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY];

Output

CREATE OR REPLACE SEQUENCE PUBLIC.CancelToStatus_Idx
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN PUBLIC.CancelToStatus.Idx';

CREATE OR REPLACE TABLE PUBLIC.CancelToStatus (
COL1 INT DEFAULT PUBLIC.CancelToStatus_Idx.NEXTVAL /*** MSC-WARNING - MSCEWI2031 - SEQUENCE -  GENERATED BY DEFAULT  START 1 INCREMENT 1 ***/
-- ** MSC-WARNING - MSCEWI1042 - Commented NOT FOR REPLICATION - THIS IS NON-RELEVANT **
--                                                                                                                                             NOT FOR REPLICATION                                                                                                                                                                 NOT NULL
);

On Primary

The ON PRIMARY option is not supported in Snowflake, so it is being removed and a warning has been added.

Source

CREATE TABLE [TABLE1](
[COL1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
 CONSTRAINT [pk_dimAddress_AddressId] PRIMARY KEY CLUSTERED ([COL1]) 
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Output

CREATE OR REPLACE TABLE PUBLIC.TABLE1(
COL1 VARCHAR(255) COLLATE 'EN-CI-AS' /*** MSC-WARNING - MSCEWI4004 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ NOT NULL
CONSTRAINT pk_dimAddress_AddressId PRIMARY KEY (COL1)
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH INDEX - THIS IS NON-RELEVANT **
--                                                                                                                                             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)                                                                                                                                                                                                                                                                                                      
-- ** MSC-WARNING - MSCEWI1042 - Commented ON PRIMARY - THIS IS NON-RELEVANT **
--                                                                                                                                                                                                                                                                                                        ON PRIMARY
);

ASC/DESC Column Sorting

The column sorting is not supported in Snowflake, the ASC or DESC keywords are being commented out. A warning is added.

Source

CREATE TABLE [TABLE1](
	[COL1] [int] NOT NULL,
 CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]

Output

CREATE OR REPLACE TABLE PUBLIC.AdmissionCategory (
COL1 INT NOT NULL
CONSTRAINT constraint1PRIMARY KEY (COL1/*** MSC-INFORMATION - MSCINF0019 - Column sorting is not supported in Snowflake ***/)                                                                                                                                                                                                ON PRIMARY
);

Computed Columns

Computed columns are supported in Snowflake, we just need to add the explicit data type in order to be able to deploy the table, for example.

Source

[Col1] AS (COL3 * COL2)

Output

Col1 DECIMAL AS (COL3 * COL2)

If the computed expression could not transformed, a warning is added, and a simple column definition with the expression return type will be used instead, like in the following example:

Source

[Col1] AS (CONVERT ([XML], ExpressionValue))

The expression CONVERT ([NUMERIC], ExpressionValue) is not supported yet by SnowConvert, so, after it is inspected, SnowConvert will determine that its type is XML, so the transformation will be

Col1 TEXT /*** MSC-INFORMATION - MSCINF0020 - Warning type for computed column was added, but computed expression 'CONVERT ([XML], ExpressionValue)' could not be upgraded to snowflake ***/

SnowConvert will run a process to determine the original expression type in SQLServer. However, the column will have the equivalent target type. In the previous example, the column type in SQLServer was XML, but the target type in Snowflake for storing an XML is TEXT. For more information about data type mapping check the data types sections.

MASKED WITH Column Option

Masking is not currently supported in Snowflake as the way it does in SQL Server. There is no direct translation for masking at this moment, so, a warning is added in those cases. For example:

Input

CREATE TABLE TABLE1
(
	[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);

Output

CREATE OR REPLACE TABLE PUBLIC.TABLE1 (
  COL1 VARCHAR(50)
-- ** MSC-WARNING - MSCEWI1042 - Commented MASKED WITH - THIS IS NON-RELEVANT **
--                            MASKED WITH (FUNCTION = 'default()')
                                                                 NULL
);

For more information related to masking in Snowflake, you could visit this documentation.

ROWGUIDCOL Column Option

ROWGUIDCOL is no longer applicable in Snowflake. It is used in SQL Server for UNIQUEIDENTIFIER types that are currently translated to VARCHAR. For example:

Input

CREATE TABLE TABLEROWID (
    [ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];

Output

CREATE OR REPLACE TABLE PUBLIC.TABLEROWID (
ROWGUID VARCHAR
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--                ROWGUIDCOL
                           NOT NULL
);

GENERATED ALWAYS AS ROW START/END Column Option

ROW START/END is not supported in Snowflake. An error is added when SnowConvert try to transform this kind of column option.

Input

CREATE TABLE TABLEROWID (
    [COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];

Output

CREATE OR REPLACE TABLE TABLEROWID (
    COL1 TIMESTAMP_NTZ(3)
--                          --** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GENERATED CLAUSE' NODE **
--                          GENERATED ALWAYS AS ROW START /*** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GeneratedAsRow' NODE ***/
                                                                                                                                                           NOT NULL
)
--                          --** MSC-WARNING - MSCEWI1042 - Commented ON PRIMARY - THIS IS NON-RELEVANT **
--                          ON PRIMARY
                          ;
PreviousDDLsNextIndex

Last updated