Emil's Wicked Cool Blog

In general, having a persistence-ignorant application provides a lot of flexibility.  Allowing us to easily port between various RDBMS’s, NoSQL data stores, text files, the cloud, or simply storing our data on papyrus managed and maintain by a group of beer-making monks provides us with tangible opportunities to cross-sell our (non-hosted) application to multiple clients.

As any write once, run anywhere scheme, support for multiple databases can be notoriously difficult.  Consider the following SQL Server T-SQL code:

Wait…what?  I just created FOO, yet SQL Server is telling me that the table foo doesn’t exist.  What’s going on?  In this case, the database default collation has been changed to Latin1_General_Bin, a binary collation, so FOO != ‘foo’.  As a result, the following query also provides no results:

To get any data back from this table, we need to match exactly:

Great, so I’ve jacked the default installation collation in SQL Server.  Not a lot of people or organizations do this?  But…is this an academic exercise?  If you want to support multiple databases and clients, the answer is a resounding NO.  Consider:

1. Some organizations may want the application to work on a database with binary collation.  Rare, but it could happen.
2. Organizations might be using another database, and not all databases are case insensitive, even by default.

If you want a portable application, #2 is your much more concerning issue.  Recently I verified default installations on a number of popular database systems to determine what behavior they produced.  Here are my findings (in alphabetical order):

	Table/Column Names	Data
Oracle	Insensitive	Sensitive
Postgres	Insensitive	Sensitive
MySQL	Sensitive	Insensitive
SQLLite	Insensitive	Sensitive
SQL Server	Insensitive	Insensitive

The most curious finding is MySQL.  I’d love to know what reasoning was used to come to the conclusion that Table and Column names should be case sensitive, but data should be considered insensitive.  In any case, programming for the least common denominator requires handling these situations. 

Ideally you’d leave the SQL to an ORM.  If your application is unlikely to switch database platforms and an ORM is out of the question, I’d recommend a RDBMS-specific data access layer.  If you’re application absolutely must have a generic data layer, though, you must keep this in mind as part of design.