Identifier requirements¶

Unquoted identifiers¶

If an identifier is not enclosed in double quotes, it must begin with a letter or underscore (_) and cannot contain extended characters or blank spaces.

The following are all examples of valid identifiers; however, the case of the characters in these identifiers would not be preserved:

myidentifier
MyIdentifier1
My$identifier
_my_identifier

Unquoted identifiers are stored and resolved in uppercase. Therefore, an unquoted identifier is equivalent to a capitalized double-quoted identifier with the same name. For example, the following two statements attempt to create the same table:

CREATE TABLE mytable(c1 INT, c2 INT);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MYTABLE successfully created. |
+-------------------------------------+

CREATE TABLE "MYTABLE"(c1 INT, c2 INT);

002002 (42710): SQL compilation error:
Object 'MYTABLE' already exists.

Double-quoted identifiers¶

Delimited identifiers (i.e. identifiers enclosed in double quotes) are case-sensitive and can start with and contain any valid characters, including:

• Numbers

• Special characters (., ', !, @, #, $, %, ^, &, *, etc.)

• Extended ASCII and non-ASCII characters

• Blank spaces

For example:

"MyIdentifier"
"my.identifier"
"my identifier"
"My 'Identifier'"
"3rd_identifier"
"$Identifier"
"идентификатор"

"My.DB"."My.Schema"."Table.1"

Identifier resolution¶

By default, Snowflake applies the following rules for storing identifiers (at creation/definition time) and resolving them (in queries and other SQL statements):

• When an identifier is unquoted, it is stored and resolved in uppercase.

• When an identifier is double-quoted, it is stored and resolved exactly as entered, including case.

For example, the following four names are equivalent and all resolve to TABLENAME:

TABLENAME
tablename
tableName
TableName

In contrast, the following four names are considered to be different, unique values:

"TABLENAME"
"tablename"
"tableName"
"TableName"

If these identifiers were used to create objects of the same type (e.g. tables), they would result in the creation of four distinct objects.

Migrating from databases that treat double-quoted identifiers as case-insensitive¶

In the ANSI/ISO standard for SQL, identifiers in double quotes (delimited identifiers) are treated as case-sensitive. However, some companies provide databases that treat double-quoted identifiers as case-insensitive.

If you are migrating your data and applications from one of these databases to Snowflake, those applications might use double quotes around identifiers that are intended to be case-insensitive. This can prevent Snowflake from resolving the identifiers correctly. For example, an application might use double quotes around an identifier in lowercase, and the Snowflake database has the identifier in uppercase.

To work around this limitation, Snowflake provides the QUOTED_IDENTIFIERS_IGNORE_CASE session parameter, which causes Snowflake to treat lowercase letters in double-quoted identifiers as uppercase when creating and finding objects.

See the next sections for details:

• Controlling case using the QUOTED_IDENTIFIERS_IGNORE_CASE parameter

• Impact of changing the parameter

TABLENAME
tablename
tableName
TableName
"TABLENAME"
"tablename"
"tableName"
"TableName"

-- Set the default behavior
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = false;

-- Create a table with a double-quoted identifier
CREATE TABLE "One" (i int);  -- stored as "One"

-- Create a table with an unquoted identifier
CREATE TABLE TWO(j int);     -- stored as "TWO"

-- These queries work
SELECT * FROM "One";         -- searches for "One"
SELECT * FROM two;           -- searched for "TWO"
SELECT * FROM "TWO";         -- searches for "TWO"

-- These queries do not work
SELECT * FROM One;           -- searches for "ONE"
SELECT * FROM "Two";         -- searches for "Two"

-- Change to the all-uppercase behavior
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = true;

-- Create another table with a double-quoted identifier
CREATE TABLE "Three"(k int); -- stored as "THREE"

-- These queries work
SELECT * FROM "Two";         -- searches for "TWO"
SELECT * FROM two;           -- searched for "TWO"
SELECT * FROM "TWO";         -- searches for "TWO"
SELECT * FROM "Three";       -- searches for "THREE"
SELECT * FROM three;         -- searches for "THREE"

-- This query does not work now - "One" is not retrievable
SELECT * FROM "One";         -- searches for "ONE"

-- Set the default behavior
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = false;

-- Create a table with a double-quoted identifier
CREATE TABLE "Tab" (i int);  -- stored as "Tab"

-- Create a table with an unquoted identifier
CREATE TABLE TAB(j int);     -- stored as "TAB"

-- This query retrieves "Tab"
SELECT * FROM "Tab";         -- searches for "Tab"

-- Change to the all-uppercase behavior
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = true;

-- This query retrieves "TAB"
SELECT * FROM "Tab";         -- searches for "TAB"