Printable View
what's different between varchar2(10) and varchar2(1000)?
when designing database, can i alway replace varchar2(10)
with varchar2(1000), because i think it have no harm to be longer, if you use that much, then use it, if not, then just leave the rest empty.
No,
If you define a varchar2 < 250, you have a length indicator of
1 byte.
If you define a varhcar2 >= 250, you have a length indicator of 3 bytes.
found in "Oracle 7 server administrator Guide, addendum A, Estimating space requirements for non-clustered tables"
Hope this helps
Gert
Good catch!
- Chris
It is a question of good design. For many columns (usernames, zip codes, phone numbers etc) you know the length. With VARCHAR2(10) you know the maximum length for the column is 10, with VARCHAR2(1000) there is a very good chance you get values with length more than 10.
I thinks besides the question of good design, the length of any field is also a part of business rules. For example, it may be a rule that name will be maximum 30 characters or address line be 3 lines of 70 characters each etc.
When the rule is not compilied with, the insert in row should fail. Without this discpilne, it is quite possible that anything goes inside database.
Kailash Pareek
I just want to add that - querying is messy.
If you start spraying varchar2(1000) all over the database - you have to format the columns before you can see anything on the screen. Imagine using substr everytime you have to write a query!
It also brings the query performance issue - using functions in where clauses negates indexes on tables.