Tables

Applies to

• SQL Server

• Azure Synapse Analytics

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 Sql Server it can come together with a constraint Name. Since that part is not supported in Snowflake, it has been removed, and a warning has been 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,
	[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

OUT -> SqlServer_06.sql

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

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "[dbo].[Name]" **
CREATE OR REPLACE TABLE SalesLT.Address (
	AddressID INT DEFAULT SalesLT.Address_AddressID.NEXTVAL NOT NULL,
	AddressLine1 VARCHAR(60) NOT NULL,
	AddressLine2 VARCHAR(60) NULL,
	City VARCHAR(30) NOT NULL,
	StateProvince VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	CountryRegion VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
	PostalCode VARCHAR(15) NOT NULL,
	rowguid VARCHAR
 	               !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
 	               ROWGUIDCOL  NOT NULL,
	ModifiedDate TIMESTAMP_NTZ(3) NOT NULL,
	CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID),
	CONSTRAINT AK_Address_rowguid UNIQUE (rowguid)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
;

Collate Column Option

For the Collate transformation, please check the following link.

Collate

ENCRYPTED WITH Column Option

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

Source

IN -> SqlServer_07.sql

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

OUT -> SqlServer_07.sql

CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
    COL1 VARCHAR(60)
--                     --** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
--                     ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

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.

Notice that NOT FOR REPLICATION is a statement that is not required in Snowflake because it is translated to an equivalent, so it is removed.

Source

IN -> SqlServer_08.sql

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

Output

OUT -> SqlServer_08.sql

CREATE OR REPLACE SEQUENCE TABLE1_COL1
    START WITH 1
    INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN TABLE1.COL1';

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

On Primary

The ON PRIMARY option is a statement that is used in SQL Server to define on which file an object, e.g. a table, is going to be created. Such as on a primary or secondary file group inside the database. Snowflake provides a different logic and indicates distinct constraints. Please review the following Snowflake documentation for more information.

Source

IN -> SqlServer_09.sql

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

OUT -> SqlServer_09.sql

CREATE OR REPLACE TABLE TABLE1 (
 COL1 VARCHAR(255) COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ NOT NULL
  CONSTRAINT pk_dimAddress_AddressId PRIMARY KEY (COL1)
 )
 COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
 ;

ASC/DESC Column Sorting

Column sorting is not supported in Snowflake, the ASC or DESC keywords are being removed.

Source

IN -> SqlServer_10.sql

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

Output

OUT -> SqlServer_10.sql

CREATE OR REPLACE TABLE TABLE1 (
	COL1 INT NOT NULL,
	 CONSTRAINT constraint1 PRIMARY KEY (COL1)
	)
	COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "09/23/2024" }}'
	;

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

IN -> SqlServer_11.sql

CREATE TABLE [TABLE1](
	[COL2] [int] NOT NULL,
	[COL2] [int] NOT NULL,
	[COL1] AS (COL3 * COL2),
)

Output

OUT -> SqlServer_11.sql

CREATE OR REPLACE TABLE TABLE1 (
	COL2 INT NOT NULL,
	COL2 INT NOT NULL,
	COL1 VARIANT AS (COL3 * COL2) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

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

Source

IN -> SqlServer_12.sql

CREATE TABLE [TABLE1](
	[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

OUT -> SqlServer_12.sql

CREATE OR REPLACE TABLE TABLE1 (
	Col1 TEXT AS (CAST(ExpressionValue AS VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XML DATA TYPE CONVERTED TO VARIANT ***/!!!)) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

SnowConvert will run a process to determine the original expression type in SQL Server. 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

In SQL Server the data masking is used to keep sensitive information from nonprivileged users. Review the SQL SERVER documentation for more information. In Snowflake, there is a dynamic data masking functionality but it is available to Enterprise Edition only. Please review the following Snowflake documentation.

Input

IN -> SqlServer_13.sql

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

Output

OUT -> SqlServer_13.sql

CREATE OR REPLACE TABLE TABLE1
(
	COL1 VARCHAR(50)
 	                !!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
 MASKED WITH (FUNCTION = 'default()') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

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

IN -> SqlServer_14.sql

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

Output

OUT -> SqlServer_14.sql

CREATE OR REPLACE TABLE TABLEROWID (
    ROWGUID VARCHAR
                    !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
                    ROWGUIDCOL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

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

IN -> SqlServer_15.sql

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

Output

OUT -> SqlServer_15.sql

CREATE OR REPLACE TABLE TABLEROWID (
    COL1 TIMESTAMP_NTZ(3) GENERATED ALWAYS AS ROW START !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GeneratedClause' NODE ***/!!! NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

Known Issues

No issues were found.

Related EWIs

1. SSC-EWI-0036: Data type converted to another data type.
2. SSC-EWI-0040: Statement Not Supported.
3. SSC-EWI-0073: Pending Functional Equivalence Review.
4. SSC-EWI-TS0017: Masking not supported.
5. SSC-FDM-0012: Constraint in default expression is not supported.
6. SSC-FDM-TS0002: This message is shown when there is a collate clause that is not supported in Snowflake.
7. SSC-FDM-TS0009: Encrypted with not supported in Snowflake.
8. SSC-FDM-TS0014: Computed column transformed.
9. SSC-FDM-TS0015: Data type is not supported in Snowflake.
10. SSC-PRF-0002: Case-insensitive columns can decrease the performance of queries.