COLUMN DEFINITION

ALTER TABLE ADD column_name

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:

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

ALTER TABLE table_name
ADD column_name INTEGER;

Snowflake

ALTER TABLE IF EXISTS PUBLIC.table_name
ADD column_name INTEGER;

COLLATE

Collation allows you to specify broader rules when talking about string comparison.

SQL Server

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

ALTER TABLE IF EXISTS PUBLIC.table_name
ADD COLUMN new_column_name VARCHAR COLLATE 'EN-CI-AS';

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

ALTER TABLE table_name
ALTER COLUMN column_name
ADD MASKED WITH ( FUNCTION = ' random(1, 999) ' );

Snowflake

/*** MSC-INFORMATION - MSCINF0034 - 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 PUBLIC.table_name 
MODIFY COLUMN column_name/*** MSC-INFORMATION - MSCINF0033 - 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

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

ALTER TABLE PUBLIC.table_name
ADD intcol INTEGER DEFAULT 0;

ALTER TABLE PUBLIC.table_name
ADD varcharcol VARCHAR(20) DEFAULT '';

ALTER TABLE PUBLIC.table_name
ADD datecol DATE
-- ** MSC-ERROR - MSCEWI1088 - EXPRESSIONS LIKE FUNCTION CALLS, VARIABLES, OR NAMED CONSTANTS ARE NOT SUPPORTED ON DEFAULT OPTION 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

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

ALTER TABLE IF EXISTS PUBLIC.table_name
ADD encryptedcol VARCHAR(20)
-- ** MSC-WARNING - MSCEWI4003 - 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

ALTER TABLE table2 ADD 
column_test INTEGER NOT NULL,
column_test2 INTEGER NULL,
column_test3 INTEGER;

Snowflake

ALTER TABLE 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

ALTER TABLE table3 ADD 
column_test INTEGER IDENTITY(1, 100) NOT FOR REPLICATION;

Snowflake

CREATE OR REPLACE SEQUENCE PUBLIC.table3_column_test
    START WITH 1
    INCREMENT BY 100
COMMENT = 'FOR TABLE-COLUMN PUBLIC.table3.column_test';

ALTER TABLE table3 ADD 
column_test INTEGER DEFAULT PUBLIC.table3_column_test.NEXTVAL 
/*** MSC-WARNING - MSCEWI1048 - SEQUENCE -  GENERATED BY DEFAULT  START 1 INCREMENT 100 ***/ 
-- ** MSC-WARNING - MSCEWI1042 - Commented NOT FOR REPLICATION - THIS IS NON-RELEVANT **
--  NOT FOR REPLICATION 
;ss

Unsupported clauses

FILESTREAM

The original behavior of FILESTREAM is not replicable in Snowflake, and merits commenting out the entire ALTER TABLE statement.

SQL Server

ALTER TABLE table2
ADD column1 varbinary(max)
FILESTREAM;

Snowflake

-- ** MSC-ERROR - MSCEWI4058 - ONE OR MORE OF THE TABLE ELEMENT PARTS ARE NOT SUPPORTED IN SNOWFLAKE: FILESTREAM CLAUSE **
--ALTER TABLE table2
--ADD column1 varbinary(max)
--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

-- 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

-- ADD COLUMN DEFINITION form
ALTER TABLE IF EXISTS PUBLIC.table3
ADD column1 INT NULL
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--                     SPARSE
 ;

----------------------------------------
/* It also applies to the other forms */
----------------------------------------

-- CREATE TABLE form
CREATE OR REPLACE TABLE PUBLIC.table3
                            (
   column1 INT
-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--                SPARSE
   NULL
);

-- ** MSC-ERROR - MSCEWI4061 - ALTER COLUMN COMMENTED OUT BECAUSE SPARSE column IS NOT SUPPORTED IN SNOWFLAKE **
--ALTER COLUMN form
--ALTER TABLE table3
--ALTER COLUMN column1 INT NULL SPARSE

ROWGUIDCOL

SQL Server

ALTER TABLE table_name
ADD column_name UNIQUEIDENTIFIER 
ROWGUIDCOL;

Snowflake

ALTER TABLE IF EXISTS PUBLIC.table_name
ADD column_name VARCHAR
-- ** MSC-WARNING - MSCEWI1040 - 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.

The 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

PreviousADDNextCOLUMN CONSTRAINT

Last updated