Identifier Requirements¶

Unquoted object identifiers:

Start with a letter (A-Z, a-z) or an underscore (“_”).
Contain only letters, underscores, decimal digits (0-9), and dollar signs (“$”).
Are case-insensitive.

If you put double quotes around an identifier (e.g. “My identifier with blanks and punctuation.”), the following rules apply:

The identifier is case-sensitive.
The identifier can contain and even start with any ASCII character from the blank character (32) to the tilde (126), excluding the double quote.

Note

Regardless of whether an identifier is unquoted or double-quoted, the maximum number of characters allowed is 255 (including blank spaces).
Identifiers can also be specified using string literals, session variables or bind variables. For details, see SQL Variables.

In this Topic:

Unquoted Identifiers
Double-quoted Identifiers
Identifier Resolution
Third-party Tools and Case-insensitive Identifier Resolution
- Controlling Case Using the QUOTED_IDENTIFIERS_IGNORE_CASE Parameter
- Impact of Changing the Parameter

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

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"
"идентификатор"

Important

If an object is created using a double-quoted identifier, when referenced in a query or any other SQL statement, the identifier must be specified exactly as created, including the double quotes. Failure to include the quotes might result in an Object does not exist error (or similar type of error).

Also, note that the entire identifier must be enclosed in quotes when referenced in a query/SQL statement. This is particularly important if periods (.) are used in identifiers because periods are also used in fully-qualified object names to separate each object.

For example:

"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.

Third-party Tools and Case-insensitive Identifier Resolution¶

Some third-party applications always use double quotes for identifiers in the SQL queries they generate, which might result in queries containing identifiers that cannot be found in Snowflake due to differences in case.

Controlling Case Using the QUOTED_IDENTIFIERS_IGNORE_CASE Parameter¶

To prevent this situation from occurring, Snowflake provides a session parameter, QUOTED_IDENTIFIERS_IGNORE_CASE, that ensures all alphabetical characters in identifiers created within the session are stored in uppercase, regardless of whether the identifier is double-quoted. The default for the parameter is FALSE.

To modify the case-sensitive behavior for double-quoted identifiers, set the parameter to TRUE for the session. Thereafter, all alphabetical characters in identifiers are stored in uppercase.

In other words, the following would all be equivalent:

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

Note that the parameter does not impact any of the limitations for unquoted identifiers with regards to numbers, extended characters, and blank spaces.

Impact of Changing the Parameter¶

Changing the QUOTED_IDENTIFIERS_IGNORE_CASE session parameter only affects new objects and queries:

With the default setting of FALSE, if an object is created using a double-quoted identifier with mixed case, Snowflake stores the identifier in mixed case.
If the parameter is then changed to TRUE, the identifier for the newly-created object is not retrievable/resolvable.

Tip

Due to the impact that changing the parameter can have on resolving identifiers, we highly recommend choosing an identifier resolution method early in your implementation of Snowflake and then dictating the default behavior by setting the parameter at the account level accordingly, which can be done by any account administrator for your account. The parameter can always be overridden at the session level, but we don’t encourage changing the parameter from the default, unless you have an explicit need to do so.

The following examples illustrate the behavior after changing the parameter from FALSE to TRUE:

-- 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"

Additionally, if the identifiers for two tables differ only by case, one identifier might resolve to a different table after changing the parameter:

-- 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"