Case insensitive columns can decrease performance of queries
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
IN -> Teradata_01.sql
CREATETABLEexampleTable( col1 CHAR(10) CASESPECIFIC, col2 CHAR(20) NOT CASESPECIFIC);
OUT -> Teradata_01.sql
CREATETABLEexampleTable( 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"}}'
;
CREATE OR REPLACETABLEexampleTable ( 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.