SSC-PRF-0002

Case insensitive columns can decrease performance of queries

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Using collation in Snowflake can affect the performance of queries, specially when used inside WHERE clauses, for more information on how collation affects performance please check the Performance Implications of Using Collation.

This warning is generated to point out that a column is created with case insensitive collation, any use of this column in queries may result in a performance degradation.

Code examples

Output

OUT -> Teradata_01.sql

CREATE TABLE exampleTable
(
    col1 CHAR(10),
    col2 CHAR(20) COLLATE 'en-ci' /*** 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":"teradata"}}'
;

Oracle

Input

IN -> Oracle_01.sql

CREATE TABLE exampleTable (
    col1 VARCHAR(50) COLLATE BINARY_CI,
    col2 VARCHAR(50) COLLATE BINARY_CS
);

Output

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE exampleTable (
       col1 VARCHAR(50) COLLATE BINARY_CI /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/,
       col2 VARCHAR(50) COLLATE BINARY_CS
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

SQL Server

Input

IN -> SqlServer_01.sql

CREATE TABLE exampleTable (
    col1 VARCHAR(50) COLLATE Latin1_General_CI_AS,
    col2 VARCHAR(50) COLLATE Latin1_General_CS_AS
);

Output

OUT -> SqlServer_01.sql

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

Recommendations

• Analyze the impact of using case-insensitive collation on the performance of your application, if it is severely affected then consider refactoring your code to avoid using it, if the performance level is tolerable then this warning can be safely ignored.

• If you need more support, you can email us at [email protected]