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
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"}}'
;
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.
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.
ENCRYPTED WITH Column Option
The Encrypted With is not supported in Snowflake, so it is being removed, and a warning is added.
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"}}'
;
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
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"}}'
;
For more information, you can also check this related to this transformation.
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 for more information.
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 .
In SQL Server the data masking is used to keep sensitive information from nonprivileged users. Review the for more information. In Snowflake, there is a dynamic data masking functionality but it is available to Enterprise Edition only. Please review the following.
ROWGUIDCOL is no longer applicable in Snowflake. It is used in SQL Server for types that are currently translated to VARCHAR. For example:
: Data type converted to another data type.
: Statement Not Supported.
: Pending Functional Equivalence Review.
: Masking not supported.
: Constraint in default expression is not supported.
: This message is shown when there is a collate clause that is not supported in Snowflake.
: Encrypted with not supported in Snowflake.
: Computed column transformed.
: Data type is not supported in Snowflake.
: Case-insensitive columns can decrease the performance of queries.