Snowflake for SQL Server Users - Part 8 - Case Sensitivity

Snowflake for SQL Server Users - Part 8 - Case Sensitivity

There are many things I like about Snowflake. How they handle case and collations is not one of them.

There are currently no rich options for handling case like you have in SQL Server, with detailed options around both collations, and case sensitivity.

I’ve previously written about how I think that case-sensitivity is a pox on computing. I see absolutely no value in case-sensitivity in business applications, and a significant downside.

Case preservation is a different thing. I expect systems to remember the case that I define things with, but 99.9% of the time, I want to search for them without caring about case. All that case-sensitivity does provide is the ability to have two objects in the same scope that differ only by different capital letters in their names. That’s usually the result of lazy coding, and almost never a good idea.

Snowflake is basically case-sensitive. I wish it wasn’t. There are, however, some workarounds.

Object Names

To get around some of the problems that case-sensitivity causes, Snowflake automatically upper-cases object names when you define or use them. By default, Snowflake treats the objects Customers, customers, and CUSTOMERS as the same object. In fact, if you execute a statement like:

CREATE TABLE Customers

what it will create is a table called CUSTOMERS.  If you execute

SELECT 2 AS Value;

you’ll get back the value 2 in a column called VALUE, not the column name that you asked for.

That breaks my basic wish (in any language) for case-preservation. Most objects that you see in almost every Snowflake presentation have names that are all capitalized.

You can, however, get around this by quoting each name with double-quotes.

CREATE TABLE “Customers”

And then you need to do that for every table, every column, every object, etc. from then on. If you execute:

SELECT 2 AS “Value”;

you’ll get the value 2 with the column name that you’re after.

To get around the idea that most people won’t want to compare strings in a case-sensitive way, they’ve created some different operators to deal with case. For example, if you use

WHERE “CustomerName” ILIKE ‘Fred%’

you get a case-insensitive version of LIKE.

Added To My Wish List

The Snowflake people really need to fix how they handle case. You can see from the requests in their user forums that I’m not the only one that thinks so.

This aspect of the product feels very Oracle-like, and also feels like being back in the 1960s. Humans don’t like screaming snake case. I really hope they will fix it soon as it’s currently one of the the weakest aspects of the product.

 

For an index to all posts in this series, see the first post here.

2019-09-27