You are viewing an older version of this section. View current production version.

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.

The engine variable table_name_case_sensitivity defines the case-sensitivity of a database object. When this variable is set to ON, 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)

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.

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);
Info

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.