The specific answer to your question (at least for Oracle and probably other databases) is that the length of the field doesn’t matter, only the length of the data. However, this shouldn’t be used as a determining factor concerning whether to set the field to its maximum allowable length or not. Here are some other issues you should consider before maxing out field sizes.
Formatting
Any client tool that formats the data based on the size of the fields will require special formatting considerations. Oracle’s SQL*Plus for example by default displays the maximum size of Varchar2 columns even if the data is only one character long. Compare…
create table f1 (a varchar2(4000), b varchar2(4000));
create table f2 (a varchar2(5), b varchar2(5));
insert into f1 values ('a','b');
insert into f2 values ('a','b');
select * from f1;
select * from f2;
Bad Data
Field length provides an additional mechanism to catch/prevent bad data. An interface shouldn’t attempt to insert 3000 characters into a 100 character field, but if that field is defined to be 4000 characters, it just might. The error woudn’t be caught at the data entry stage, but the system may have trouble further down when another application tries to process the data and chokes. As an example, if you later decide to index the field in Oracle you would exceed the maximum key length (depending on block size and concatenation). See…
create index i1 on f1(a);
Memory
If the client application allocates memory using the maximum size, the application would allocate significantly more memory than is necessary. Special considerations would have to be done to avoid this.
Documentation
The size of the field provides another data point of documentation about the data. We could call all tables t1, t2, t3, etc. and all fields f1, f2, f3, etc., but by specifying meaningful names we better understand the data. For example, if an address table for a company with customers in the U.S. has a field called State that is two characters we expect the two character state abbreviation to go in it. On the other hand if the field is one hundred characters we might expect the full state name to go in the field.
All that being said, it does seem prudent to be prepared for change. Just because all your product names today fit in 20 characters doesn’t mean they always will. Don’t go overboard and make it 1000, but do leave room for plausible expansion.
