Database Object Case-Sensitivity

Database objects allow you to store, reference, and operate on data. For example, tables store data, views and indexes reference data, and stored procedures and functions operate on data. Columns are not database objects.

The engine variable table_name_case_sensitivity defines the case-sensitivity of a database object. When this variable is set to ON (the default setting), all database objects are case-sensitive, except:

• Stored procedures

• User-defined scalar-valued functions (UDFs)

• User-defined aggregate functions (UDAFs)

• information_schema table names

When the variable is set to OFF, the four database objects noted in the previous list are case-insensitive, in addition to the following database objects.

• Tables

• Views

• Table aliases

• User-defined table-valued functions (TVFs)

• External functions

Pipeline names are always case-sensitive.

The following example shows how you can refer to an existing table test_table. This example assumes table_name_case_sensitivity is set to OFF.

SELECT @@table_name_case_sensitivity;

+-------------------------------+
| @@table_name_case_sensitivity |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.04 sec)

INSERT INTO Test_Table(a) VALUES (10);

SELECT * FROM TEST_Table;

When you create a database object which is not case-sensitive, you must use a unique and case-independent name. For example, when table_name_case_sensitivity is set to OFF, running the following two commands results in an error:

CREATE TABLE test_table_2(a INT);

CREATE TABLE test_TABLE_2(a INT);

The following example illustrates that column names are not subject to case-sensitivity even when table_name_case_sensitivity is set to ON:

select @@table_name_case_sensitivity;
+-------------------------------+
| @@table_name_case_sensitivity |
+-------------------------------+
|                             1 |
+-------------------------------+

CREATE TABLE test_table_3 (a int, b INT);

INSERT INTO test_table_3 (a, b) VALUES (1, 2);

SELECT a, b FROM test_table_3;

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

SELECT A, B FROM test_table_3;

+------+------+
| A    | B    |
+------+------+
|    1 |    2 |
+------+------+