SQL Operations Guide

Case sensitivity in SQL databases

A FairCom SQL database is case-sensitive by default. A case-sensitive database can safely store and retrieve UTF-8 characters; thus, FairCom recommends you create case-sensitive SQL databases.

Case sensitivity applies to string comparisons in queries, expressions, values stored in indexes, and identifiers.

Although the SQL standard provides functions and syntax for case-insensitive string comparisons in a case-sensitive database, using a function for case-insensitive comparisons can significantly slow down a SQL query because the engine will not use an index on the field. This limitation applies if you use a function over a field on the right or left side of a comparison, in an ORDER BY, or in a JOIN clause.

If your application only uses ASCII characters and wants all string comparisons to be case-insensitive, you may want to create a case-insensitive SQL database.

Consequences of creating a case-insensitive SQL database

You can create a case-insensitive SQL database if you exclusively use case-insensitive ASCII characters. A case-insensitive database irreversibly changes how it stores some string characters:

• The SQL database stores string identifiers in SQL dictionaries as lowercase characters unless the database is case-sensitive and you enclose the identifier in double quotes, such as "my_TABLE".
• The SQL database stores string values in indexes as uppercase.
• The SQL database performs all string comparisons in expressions and queries using case-insensitivity.
• You cannot change the case sensitivity of a SQL database after you create it.
• NOTE: You cannot use UTF-8 characters because the database changes case using ASCII rules rather than UTF-8 ones.

How to create a case-insensitive SQL database

You change the server configuration keyword to SQL_OPTION DB_CASE_INSENSITIVE to cause the server to create all new SQL databases as case-insensitive. The server configuration keyword applies to all newly created SQL databases. It cannot change existing databases.

The ctsqlcdb command-line utility can create a case-insensitive database; it cannot change an existing database.

The fc_createdb stored procedure allows you to create a case-insensitive database; it cannot change an existing one.

How does case sensitivity affect SQL identifiers?

Following the SQL standard, a case-sensitive SQL database treats all identifiers as case-insensitive unless you enclose them in double quotes. For example, myTABLE equals mytable, and "myTABLE" does not equal mytable. Thus, your SQL statements can choose to be case-sensitive or case-insensitive.

A case-insensitive SQL database treats all identifiers as case-insensitive, even when you enclose them in double quotes. For example, myTABLE equals mytable, and "myTABLE" equals myTABLE. Thus, your SQL statements are always case-insensitive.

How does case sensitivity interact with the JSON, CTDB, and ISAM APIs?

If you use a case-insensitive SQL database to create indexes, they will store string field values as uppercase. Other APIs give you the choice in the case sensitivity of indexes. You must ensure that indexes created outside of SQL are compatible with SQL.

The SQL dictionary for a case-insensitive SQL database stores identifiers in lowercase. The NoSQL APIs store identifiers as case-sensitive. If your code works with SQL and NoSQL APIs, you must be aware of this difference.