Collate

The transformation of the collate depends on its value, since it can be supported or not supported.

Currently, these are the languages that are supported for the transformation, if they are found in the collate, they will be transformed into its Snowflake equivalent.

SqlSever	Snowflake
Latin1_General	EN
Modern_Spanish	ES
French	FR

If the language is not one of the above, the collate will be commented.

Also, since the collate in SqlServer comes with additional specifications, like CI, CS, AI, and AS, only these are supported, if there are more and are not supported, they will be commented in the result.

Source

IN -> SqlServer_01.sql

SELECT 'a' COLLATE Latin1_General_CI_AS;

SELECT 'a' COLLATE Modern_Spanish_CI_AS;

SELECT 'a' COLLATE French_CI_AS;

SELECT 'a' COLLATE Albanian_BIN;

SELECT 'a' COLLATE Latin1_General_CI_AS_WS;

SELECT 'a' COLLATE Latin1_General_CI_AS_KS_WS;

SELECT 'a' COLLATE Albanian_CI_AI;

Expected

OUT -> SqlServer_01.sql

SELECT 'a' COLLATE 'EN-CI-AS';

SELECT 'a' COLLATE 'ES-CI-AS';

SELECT 'a' COLLATE 'FR-CI-AS';

SELECT 'a'
--           --** SSC-FDM-TS0001 - COLLATION Albanian_BIN NOT SUPPORTED **
--           COLLATE Albanian_BIN
                               ;

SELECT 'a' COLLATE 'EN-CI-AS' /*** SSC-FDM-TS0002 - COLLATION FOR VALUE WS NOT SUPPORTED ***/;

SELECT 'a' COLLATE 'EN-CI-AS' /*** SSC-FDM-TS0002 - COLLATION FOR VALUES KS,WS NOT SUPPORTED ***/;

SELECT 'a'
--           --** SSC-FDM-TS0001 - COLLATION Albanian_CI_AI NOT SUPPORTED **
--           COLLATE Albanian_CI_AI
                                 ;

Let's see an example of collate in a Create Table

Source

IN -> SqlServer_02.sql

CREATE TABLE TABLECOLLATE
(
    COL1 VARCHAR COLLATE Latin1_General_CI_AS
);

Expected

OUT -> SqlServer_02.sql

CREATE OR REPLACE TABLE TABLECOLLATE
(
    COL1 VARCHAR COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

As you can see, the transformation of Collate inside a Select or a Table is the same.

Related EWIS

1. SSC-FDM-TS0001: This message is shown when there is a collate clause that is not supported in Snowflake.

2. SSC-FDM-TS0002: This message is shown when there is a collate clause that is not supported in Snowflake.

3. SSC-PRF-0002: Case-insensitive columns can decrease the performance of queries.