Thanks for the question, hahaer.
Asked: July 22, 2001 - 10:31 pm UTC
Last updated: February 17, 2006 - 1:45 pm UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
One senior developer of our company defines his table column length at some random. E.g. he would define an employee table as follows:
CREATE TABLE employee {
id number,
name varchar2(200),
job varchar2(200),
hobby varchar2(200),
education varchar2(200),
deptment varchar2(200)
);
All VARCHAR2 columns are 200 bytes long, and no length limit on id column even we can not have employees more than 20000. He said it was simple to define columns in this way and because columns were VARCHAR2 type, they would be saved as they were, and wouldn't waste much space, not like CHAR type. So could u tell me what benifits can I get if I define column length as precise as possible so I can persuade him? It will save a lot of memory when DBWR loads data into buffer cache? Thanks.
and Tom said...
Well, does this programmer use primary keys? constraints? triggers? any data integrity constraints?
If so, tell him to consider the length of a field yet another data integrity constraint. Thats what it is.
If the id is a number between 1 and 20,000, it should technically be a number(5) -- perhaps with a check constraint. The more DECLARITIVE you can be the better.
Consider it is 2 years from now, this "SR" developer has decided to move on to greener pastures. Your new programmer is left with that table. Unbeknownst to him, the name field is really limited to 80 bytes, job is a 10 character field, hobby is 125 characters max. All of this documentation is LOST, missing, not there.
At some point, someone will stuff 20 characters into job, which will crash some other existing program that is expecting 10 characters max.
The table structure is meta-data, it describes the maximum width of these fields. Your SR developer is ignoring the self documenting features here. They should most definitely be supplying valid values there.
It is not wasting any runtime memory, it is only contributing to bugs and errors down the road. It is the wrong approach, tell them to take the time to do it right.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment