Introduction
It seems that Snowflake has a different way handling naming of objects than SQL Server or Azure SQL Database. I want to show you some examples that I tested so far in snowflake.
The bottomline is : When you create a mixed case object (eg. table) with "" you can only reference this table with the same casing (case sensitive) and when you use no "" OR use "" and put the name in uppercase, the object is created in uppercase and you can reference this object case insensitive.
I've experimented with upper-, lower, mixed case variations in the naming of object and you can see what happens when I try to query them.
Experiment #1.1
In this experiment I have created a table with mixed case and double quotes. The double quotes are important here. The double quotes makes sure that the table is created in mixed case and not in uppercase. This happens when the quotes are omitted.
CREATE TABLE IF NOT EXISTS dbo."MiXed tAbLe"( ID integer CONSTRAINT PK_Product_ID PRIMARY KEY ENFORCED );
This results in the table in the following screenshot of Snowflake. The table is created as mixed case.
Experiment #1.2
Can we select the table with the same mixed case naming and the quotes? Yes we can!
Experiment #1.3
Experiment #1.4
Can we reference the table without ""? I have created the table with a space and therefore it will give an error.
Therefore, I've created another experiment. You can read this in the following experiment.
Experiment #2.1
In this experiment I created a table with mixed case, but without a space in the name and with double quotes. Now when I tried to reference the the table with upper case of the name it resulted in the following error.
Experiment #2.2
Another experiment shows that when I reference the table with the same casing but without the "", still an error occurs.
Experiment #2.3
Referencing the table with double quotes and the same casing will return the table.
This also implies that when double quotes have been used for creating the table you have to use the double quotes in the SELECT statement. As we see later in this blogpost, when the table is created in uppercase and with "" we can reference the table in uppercase, too.
Experiment #2.4
Now an experiment with the double quotes and with other casing than the casing when the table was created. An error happens.
Experiment #3.1
A new experiment with the mixed casing and without double quotes. What happens when we create a table in mixed casing and without the double quotes? The table is upper cased.
You can see that here : UPPERTABLE
Experiment #3.2
When the same casing is used in the SELECT statement the table is succesfully queried by the SELECT statement.
Experiment #3.3
So, what happens when the table is queried with other casing (mixed case)? The query returns successfully results.
Experiment #4.1
A new experiment with double quotes and uppercasing. I created a table with upper casing and with quotes and queried the table with the same casing.
Experiment #4.2
And now query the table with the upper case name but without double quotes? Yes this gives also a successful result.
Experiment #4.3
Can we query the table with mixed case and with double quotes? No that is not possible.
Experiment #4.4
And now the final test: can we query the table with different casing, without quotes and with a table that is created with upper case and double quotes? Yes, we can..
Experiment #5
I also tried the setting "DEFAULT_DDL_COLLATION" during the creation of the database.
-- Create the Database CREATE DATABASE IF NOT EXISTS "MiXedCaseCollation" DEFAULT_DDL_COLLATION = 'en-ci';
But that didn't change the outcome of the experiments that I have conducted.
Experiment #6
I was notified by someone of Snowflake about the setting QUOTED_IDENTIFIERS_IGNORE_CASE.
If the setting is changed to TRUE, Snowflake will automatically uppercase all the objects, and it makes no difference whether a "" is used or not.
alter session set quoted_identifiers_ignore_case = true;
All objects are now created as uppercase.
And all of the queries I've experimented are now runnable on the tables. No errors.
Conclusion
Let's see whether we can draw some conclusions from these experiments:
- When a table is created without double quotes, the table can be referenced by mixed case.
- When the table is created with double quotes, and with mixed case, the table can only be referenced with the same casing and with double quotes.
- When the table is created with double quotes and the name is in upper case, the table can be queried with mixed case.
Changing the collation of the database to case insensitive collation didn't do anything for the naming of objects.
Geen opmerkingen:
Een reactie posten