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?
