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:


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.

Case-Related Comparisons

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.






6 thoughts on “Snowflake for SQL Server Users – Part 8 – Case Sensitivity”

  1. Thanks for your posts about Snowflake for SQL Server Users. As a very traditional SQL Server DBA, I learned a lot from your posts.

    Regarding the case sensitive topic, I notice that behavior when working on the TABLE() function. The double-quoted identifiers are resolved to case sensitive object names, while the unquoted ones are resolved to all upper cases. Have to say, it's quite confusing for SQL Server users.


    — This one works
    SELECT *
    LIMIT 20;

    — This one works
    SELECT *
    LIMIT 20;

    — This one works
    SELECT *
    LIMIT 20;

    — This one doesn't work, the object doesn't exist
    SELECT *
    LIMIT 20;

    1. Yes, I can tell from the forums that a number of people are having issues with case. Apparently recently, renaming constraints didn't work as expected.

    1. Hi Chris, that's really great news if you can now set the collation at the DB level. At the time of writing this post, it was not possible, and so very many people were asking about it. I was also told at the time, that they weren't planning to change it. I think case-sensitivity as a default is just lazy coding. All it says is that I can have two values in the same scope that differ only by case. In almost every business app on the planet, that's an extraordinarily bad idea. There is the odd scenario where it's needed, but they are rare, and certainly not the default.

      Case-preservation is also important. If I define identifiers with the case that I want, I expect them to come back the same way. Again, the SF default was to return them all in upper-case (shouting case), unless I used quoting options. I think that's a poor default as well. Has that changed as yet?

Leave a Reply

Your email address will not be published. Required fields are marked *