Databases - Why case insensitive?

Question

I saw one or two threads talking globally about case sensitivity, but my question is more specific.

I understand the interest of case insensitive searches on text values for example.

But why would we use case-insensitive database names, tables and columns?

Isn't it going to lead to mistakes? Script languages that use databases are all case-sensitive, so for example if we didn't use the right case for a field it will not be found...

Answer 1

The SQL:2008 and SQL-99 standards define databases to be case-insensitive for identifiers unless they are quoted. I've found most ORMs will quote identifiers in the SQL they generate.

However, as you probably know not all relational databases strictly adhere to the standards. DB2 and Oracle are 100% compliant. PostgreSQL is mostly compliant, except for the fact that it automatically lowercases anything that isn't quoted (which personally I prefer.)

mySQL gets a bit weird, since it stores each table as a file on the file system. For this reason, it's subject to the case sensitivity of the file system. On Windows:

CREATE TABLE FOO (a INTEGER);
CREATE TABLE 'Foo' (a INTEGER); -- Errors out, already exists

Where-as on Linux:

CREATE TABLE FOO (a INTEGER); -- Creates FOO table
CREATE TABLE 'Foo' (a INTEGER); -- Creates Foo table

SQL Server is even stranger. It will preserve the case on creation, however let you refer to it in any way after (even if you quote the name!) You can't create two tables whose only difference is their casing. Note: SQL Server does have configuration options that control this stuff though, as case-sensitivity of identifiers will depend on the default collation of the database instance. How confusing!

While for the most part I agree with you that computers (programming languages, databases, file systems, URLs, passwords, etc) should be case-sensitive, all systems are implemented independently and may or may not adhere to standards that may or may not exist. Implementing a case-senstive database is definitely possible, if you know the ins and outs of your particular database system and how it behaves.

It's really your responsibility to implement your system in a way that works for you, and not the entire technology industry to implement everything in a consistent way to make your life easier.

Answer 2

The main advantage of using case sensitivity is that when we deploy it on the client site, our DB works regardless whether the client's SQL Server is set up case sensitive or not, so yes it really isn't a good idea and I don't know why anyone would use case-insensitve database tables/columns.