so uh

everywhere i've ever been-- people use the notation 003 instead of
storing something as an integer.

why do people do this?

i just see it like EVERYWHERE.. pisses me off personally.

Storing a char(3) is _MUCH_ slower than storing a tinyint right?

It depends. For instance, let me give you this number: 18972. What if I
told you this was supposed to represent a SSN like 000-01-8972? I see
people converting SSN's and ZIPs to INT format occasionally and we always
end up jumping through hoops to Join the column to other data sources that
preserve the left-most 0's.

Also, in your example, a TINYINT won't hold 256 or 999. At that point
you're off to at least a SMALLINT, which is only one byte less storage than
your CHAR(3).

[quoted text, click to view]

yeah i know that i just dont get why 50% of the db people in the world
aren't outraged by this

i think that this is a crime against databases

storing a numeric field as text?
i just dont get it-- why do people let it happen?


is it all from IBM?

is this all oracles fault?

screw leading zeros

Mostly it's probably held over from old 3GL languages like COBOL, which
tended to treat just about everything as a series of characters. A lot of
financial companies/banks/government agencies/etc. are still running off
that old COBOL code, modified slightly for 4-digit years in 1999. All this
adds up to carrying over idosyncracies that 'made sense at the time', but
are completely useless in a modern DBMS with strongly typed columns.

My major complaints about the holdovers from the old 3GL's include:

-Naming conventions. Ever wonder why every programmer in the universe uses
the letter 'i' to represent a loop counter variable in every program they
ever write? It's FORTRAN's fault. Or why people name tables tbl_Table? We
know it's a table, so why the asinine prefix "tbl_"?

-Spaghetti-code. T-SQL makes it very easy to write un-manageable code.

-CURSORS. Unfortunately programmers tend to think problems through in terms
of procedural, iterative loops of code. SQL is a declarative set-based
language. 99.9999999999999999999...% of the things people write cursors and
exotic loops to accomplish can be done several orders of magnitude faster
and simpler using SQL's set-based operations. A big thank you goes out to
all the old COBOL programmers who couldn't wrap their minds around 'SELECT'
for this one.

Like I said, the carry-over of '003' might be to allow the application to
interface with some arcane datasources that would otherwise require CASTing
and appending the lead 0's on the front with every statement. I'm not
familiar with your particular situation, but converting it to a SMALLINT
could mean you have to do something like this:

SELECT *
FROM New_Data INNER JOIN Archaic_Data
ON RIGHT('000' + CAST(New_Data.Column1 AS VARCHAR(3)), 3) =
Archaic_Data.Column1

Then again, maybe the DBA's and application programmers are just lazy...

[quoted text, click to view]

cool.. i've just seen that 100 different times and it drives me crazy

had to finally ask :)

btw, i used tbl for the longest time--

but now i use D_ for dim tables and F_ for fact tables

always use it for views and sprocs of course.. i have to :)

-aaron

Unfortunately a lot of teachers teach using 'tbl_' prefixes for tables. I'm
guilty of using the "Lookup_" and "Junction_" prefix for tables that I use
as 'lookup' and 'junction' tables respectively. The only justification I
really have for these prefixes on table names is so that they all show up
together in Enterprise Manager, which makes admin a little bit easier. Same
for Views, UDF's and SP's - in those instances prefixes prevent you from
accidentally using "no-no" prefixes like "sp" which can cause your cache hit
ratio to go wayyyyy down.

If you want to investigate this further, Celko has a new book out called SQL
Programming Style that goes into detail on the ANSI/ISO and other standards
for naming conventions, encoding schemes, etc. It's a very good book, and
he provides excellent backup when you are telling someone their naming
conventions are all screwy :)

[quoted text, click to view]