DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: varchar2

  1. #1
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    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.

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Good catch!

    - Chris

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    75

    varchar2

    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

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width