Additionally, a couple other parts are partially supported, and require additional work to be implemented in order to properly emulate the original functionality. Specifically, we're talking about the MASKED WITH property, which will be covered in the patterns section of this page.
ADD [ COLUMN ] <col_name> <col_type>
[ { DEFAULT <expr> | { AUTOINCREMENT | IDENTITY } [ { ( <start_num> , <step_num> ) | START <num> INCREMENT <num> } ] } ]
/* AUTOINCREMENT (or IDENTITY) supported only for columns with numeric data types (NUMBER, INT, FLOAT, etc.). */
/* Also, if the table is not empty (i.e. rows exist in the table), only DEFAULT can be altered. */
[ inlineConstraint ]
[ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col1_name> , cond_col_1 , ... ) ] ]
Sample Source Patterns
Basic pattern
This pattern showcases the removal of elements from the original ALTER TABLE.
SQL Server
IN -> SqlServer_01.sql
ALTER TABLE table_name
ADD column_name INTEGER;
Snowflake
OUT -> SqlServer_01.sql
ALTER TABLE IF EXISTS table_name
ADD column_name INTEGER;
COLLATE
Collation allows you to specify broader rules when talking about string comparison.
SQL Server
IN -> SqlServer_02.sql
ALTER TABLE table_name
ADD COLUMN new_column_name VARCHAR
COLLATE Latin1_General_CI_AS;
Since the collation rule nomenclature varies from SQL Server to Snowflake, it is necessary to make adjustments.
Snowflake
OUT -> SqlServer_02.sql
ALTER TABLE IF EXISTS table_name
ADD COLUMN new_column_name VARCHAR COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/;
MASKED WITH
This pattern showcases the translation for MASKED WITH property. CREATE OR REPLACE MASKING POLICY is inserted somewhere before the first usage, and then referenced by a SET MASKING POLICY clause.
The name of the new MASKING POLICY will be the concatenation of the name and arguments of the original MASKED WITH FUNCTION, as seen below:
SQL Server
IN -> SqlServer_03.sql
ALTER TABLE table_name
ALTER COLUMN column_name
ADD MASKED WITH ( FUNCTION = ' random(1, 999) ' );
Snowflake
OUT -> SqlServer_03.sql
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0057 - MASKING ROLE MUST BE DEFINED PREVIOUSLY BY THE USER ***/!!!
CREATE OR REPLACE MASKING POLICY "random_1_999" AS
(val SMALLINT)
RETURNS SMALLINT ->
CASE
WHEN current_role() IN ('YOUR_DEFINED_ROLE_HERE')
THEN val
ELSE UNIFORM(1, 999, RANDOM()) :: SMALLINT
END;
ALTER TABLE IF EXISTS table_name MODIFY COLUMN column_name!!!RESOLVE EWI!!! /*** SSC-EWI-TS0056 - A MASKING POLICY WAS CREATED AS SUBSTITUTE FOR MASKED WITH ***/!!! SET MASKING POLICY "random_1_999";
DEFAULT
This pattern showcases some of the basic translation scenarios for DEFAULT property.
SQL Server
IN -> SqlServer_04.sql
ALTER TABLE table_name
ADD intcol INTEGER DEFAULT 0;
ALTER TABLE table_name
ADD varcharcol VARCHAR(20) DEFAULT '';
ALTER TABLE table_name
ADD datecol DATE DEFAULT CURRENT_TIMESTAMP;
Snowflake
OUT -> SqlServer_04.sql
ALTER TABLE IF EXISTS table_name
ADD intcol INTEGER DEFAULT 0;
ALTER TABLE IF EXISTS table_name
ADD varcharcol VARCHAR(20) DEFAULT '';
ALTER TABLE IF EXISTS table_name
ADD datecol DATE
-- --** SSC-FDM-TS0011 - DEFAULT OPTION NOT ALLOWED IN SNOWFLAKE **
-- DEFAULT CURRENT_TIMESTAMP
;
ENCRYPTED WITH
This pattern showcases the translation for ENCRYPTED WITH property, which is commented out in the output code.
ALTER TABLE IF EXISTS table_name
ADD encryptedcol VARCHAR(20)
--** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
ENCRYPTED WITH
(COLUMN_ENCRYPTION_KEY = key_name,
ENCRYPTION_TYPE = RANDOMIZED , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);
NOT NULL
The SQL Server NOT NULL clause has the same pattern and functionality as the Snowflake NOT NULL clause
SQL Server
IN -> SqlServer_06.sql
ALTER TABLE table2 ADD
column_test INTEGER NOT NULL,
column_test2 INTEGER NULL,
column_test3 INTEGER;
Snowflake
OUT -> SqlServer_06.sql
ALTER TABLE IF EXISTS table2 ADD column_test INTEGER NOT NULL, column_test2 INTEGER NULL, column_test3 INTEGER;
IDENTITY
This pattern showcases the translation for IDENTITY. The NOT FOR REPLICATION portion is removed in Snowflake.
SQL Server
IN -> SqlServer_07.sql
ALTER TABLE table3 ADD
column_test INTEGER IDENTITY(1, 100) NOT FOR REPLICATION;
Snowflake
OUT -> SqlServer_07.sql
CREATE OR REPLACE SEQUENCE table3_column_test
START WITH 1
INCREMENT BY 100
COMMENT = 'FOR TABLE-COLUMN table3.column_test';
ALTER TABLE table3
ADD COLUMN column_test INTEGER DEFAULT table3_column_test.NEXTVAL ;
Unsupported clauses
FILESTREAM
The original behavior of FILESTREAM is not replicable in Snowflake, and merits commenting out the entire ALTER TABLE statement.
SQL Server
IN -> SqlServer_08.sql
ALTER TABLE table2
ADD column1 varbinary(max)
FILESTREAM;
Snowflake
OUT -> SqlServer_08.sql
ALTER TABLE IF EXISTS table2
ADD column1 VARBINARY(max)
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FILESTREAM;
SPARSE
In SQL Server, SPARSE is used to define columns that are optimized for NULL storage. However, when we're using Snowflake, we are not required to use this clause.
Snowflake performs optimizations over tables automatically, which mitigates the need for manual user-made optimizations.
SQL Server
IN -> SqlServer_09.sql
-- ADD COLUMN DEFINITION form
ALTER TABLE table3
ADD column1 int NULL SPARSE;
----------------------------------------
/* It also applies to the other forms */
----------------------------------------
-- CREATE TABLE form
CREATE TABLE table3
(
column1 INT SPARSE NULL
);
-- ALTER COLUMN form
ALTER TABLE table3
ALTER COLUMN column1 INT NULL SPARSE;
Snowflake
OUT -> SqlServer_09.sql
-- ADD COLUMN DEFINITION form
ALTER TABLE IF EXISTS table3
ADD column1 INT NULL
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!! SPARSE;
----------------------------------------
/* It also applies to the other forms */
----------------------------------------
-- CREATE TABLE form
CREATE OR REPLACE TABLE table3
(
column1 INT
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
SPARSE NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
-- ALTER COLUMN form
ALTER TABLE IF EXISTS table3
ALTER COLUMN column1
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0061 - ALTER COLUMN COMMENTED OUT BECAUSE SPARSE COLUMN IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
INT NULL SPARSE;
ROWGUIDCOL
SQL Server
IN -> SqlServer_10.sql
ALTER TABLE table_name
ADD column_name UNIQUEIDENTIFIER
ROWGUIDCOL;
Snowflake
OUT -> SqlServer_10.sql
ALTER TABLE IF EXISTS table_name
ADD column_name VARCHAR
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
ROWGUIDCOL;
Known Issues
1. Roles and users have to be previously set up for masking policies
Snowflake's Masking Policies can be applied to columns only after the policies were created. This requires the user to create the policies and assign them to roles, and these roles to users, in order to work properly. Masking Policies can behave differently depending on which user is querying.
SnowConvert does not perform this setup automatically.
2. Masking policies require a Snowflake Enterprise account or higher.
higher-rankThe Snowflake documentation states that masking policies are available on Entreprise or higher rank accounts.
SQL Server's DEFAULT property is partially supported by Snowflake, as long as its associated value is a constant.
4. FILESTREAM clause is not supported in Snowflake.
The entire FILESTSTREAM clause is commented out, since it is not supported in Snowflake.
5. SPARSE clause is not supported in Snowflake.
The entire SPARSE clause is commented out, since it is not supported in Snowflake. When it is added within an ALTER COLUMN statement, and it's the only modification being made to the column, the entire statement is removed since it's no longer adding anything.
