MSCCP0007

Case insensitive columns can decrease performance of queries

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

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

Teradata

Input

CREATE TABLE exampleTable
(
    col1 CHAR(10) CASESPECIFIC,
    col2 CHAR(20) NOT CASESPECIFIC
);

Output

CREATE TABLE exampleTable
(
    col1 CHAR(10),
    col2 CHAR(20) COLLATE 'en-ci' /*** MSC-WARNING - MSCCP0007 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/
);

Oracle

Input

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

Output

CREATE OR REPLACE TABLE exampleTable (
       col1 VARCHAR(50) COLLATE BINARY_CI /*** MSC-WARNING - MSCCP0007 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/,
       col2 VARCHAR(50) COLLATE BINARY_CS
   );

SQL Server

Input

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

Output

CREATE OR REPLACE TABLE exampleTable (
    col1 VARCHAR(50) COLLATE 'EN-CI-AS' /*** MSC-WARNING - MSCCP0007 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/,
    col2 VARCHAR(50) COLLATE 'EN-CS-AS'
);

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 [e-mail]