COLUMN DEFINITION

ALTER TABLE ADD column_name

Applies to

• SQL Server

• Azure Synapse Analytics

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

Description

Specifies the properties of a column that are added to a table by using ALTER TABLE.

Adding a column definition in Snowflake does have some differences compared to SQL Server.

For instance, several parts of the SQL Server grammar are not required or entirely not supported by Snowflake. These include:

• FILESTREAM

• ROWGUIDCOL

• ENCRYPTED WITH ...

• SPARSE

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.

Syntax in SQL Server

column_name <data_type>  
[ FILESTREAM ]  
[ COLLATE collation_name ]   
[ NULL | NOT NULL ]  
[
    [ CONSTRAINT constraint_name ] DEFAULT constant_expression [ WITH VALUES ]   
    | IDENTITY [ ( seed , increment ) ] [ NOT FOR REPLICATION ]   
]
[ ROWGUIDCOL ]   
[ SPARSE ]   
[ ENCRYPTED WITH  
  ( COLUMN_ENCRYPTION_KEY = key_name ,  
      ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED } ,   
      ALGORITHM =  'AEAD_AES_256_CBC_HMAC_SHA_256'   
  ) ]  
[ MASKED WITH ( FUNCTION = ' mask_function ') ]  
[ <column_constraint> [ ...n ] ]  

Snowflake

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

--** SSC-FDM-TS0022 - 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/*** SSC-FDM-TS0021 - 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
                 !!!RESOLVE EWI!!! /*** SSC-EWI-TS0078 - 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.

SQL Server

IN -> SqlServer_05.sql

ALTER TABLE table_name
ADD encryptedcol VARCHAR(20)
ENCRYPTED WITH  
  ( COLUMN_ENCRYPTION_KEY = key_name ,  
      ENCRYPTION_TYPE = RANDOMIZED ,  
      ALGORITHM =  'AEAD_AES_256_CBC_HMAC_SHA_256'  
  );

Snowflake

OUT -> SqlServer_05.sql

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;

ALTER TABLE IF EXISTS table2 ADD column_test2 INTEGER NULL;

ALTER TABLE IF EXISTS table2 ADD 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
!!!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.

For further details visit CREATE MASKING POLICY — Snowflake Documentation.

3. DEFAULT only supports constant values

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.

Related EWIs

1. SSC-EWI-0040: Statement Not Supported.
2. SSC-EWI-TS0061: ALTER COLUMN not supported.
3. SSC-EWI-TS0078: Default value not allowed in Snowflake.
4. SSC-FDM-TS0009: Encrypted with not supported in Snowflake.
5. SSC-FDM-TS0021: A MASKING POLICY was created as a substitute for MASKED WITH.
6. SSC-FDM-TS0022: The user must previously define the masking role.
7. SSC-PRF-0002: Case-insensitive columns can decrease the performance of queries.