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. Things like columns and indexes are not objects.
Note
You can only change the setting of the variable table_name_case_sensitivity
when the cluster is empty. That is, the cluster must contain no user databases.
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);