The Downside of Down Casing

ANSI standard naming in databases is to Upper Case the names of tables and columns in the database. So, for the table users (see the statement below), the table name and the two column names should be stored in the database as USERS, USERID, and NAME.

create table users (UserID int, Name Char(60))

Now, to make things “easy”, ANSI standard databases also Upper Case references to tables and columns automatically. So the statement select userid from users would be automatically converted to select USERID from USERS as it is optimized, so that it will not fail.

In DB2 and Netezza if you run select userid from users or select USERID from users, or select UserID from Users , you get all users in the table. No matter what mix of case you use for the column named UserID, you get the same results, unless you enclose the table name or column name in quotes. If the name is enclosed in quotes, then the case is preserved, and must match exactly.

So, for the table users2 created like

create table “USERS2” (“USERID” int, “NAME” char(60))

Could be accessed in DB2 and Netezza using any of the following SQL statement, because of the way DB2 adheres to ANSI standards and Upper Cases the names.

         select USERID from USERS2

         select userid from users2

         select “USERID” from “USERS2”

         select “USERID” from users2

         etc.

For databases that down case the table and column names, 3 of the above 4 statements would fail on the USERS2 table, and only the statement select “USERID” from “USERS2” would run.

Isn’t the way that DB2 and Netezza work a lot more intuitive, and a lot easier? And since you do not need to worry about the way that the SQL was written in your existing application, this is a lot less work to make your existing applications and BI report run.

Why cause more work for yourself?