Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Text vs Numeric Field

    We have a little debate going on at the office now. I was once taught to have all fields be text by default, unless some sort of mathematical calculation was required on the field. I have heard other claims that even if no calculations are expected to be made using the field, if it is going to contain numbers only it should be kept as a numeric field for faster DB performance.

    In A2K, is there a performance difference between how it processes text vs numeric fields? Also, is there a significant difference in the size of the overall database? (I view fighting unnecessary bloat as one way of maximizing performance.)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text vs Numeric Field

    The rule of thumb is that you only use numeric fields when you're going to do calculations on them OR for keys. If you fall into the trap of using a numeric field for things like zip codes, it will bite you when you get to the east coast and the zips start with a zero.

    However, numeric fields are much faster as indexes, which may be where the argument is coming from. I refuse to make fields numeric simply because they contain numbers. Zip codes, phone numbers and social security numbers are numeric, but they should always be stored as text. They should NOT be used as primary keys, and you won't usually see a significant slowdown from storing them as text, since they aren't the primary keys.

    I use autonumbers are keys, which means that they will still be numeric when used as a foreign key, so performance will not suffer.

    A2K processes text and numeric fields the same way earlier versions did. The big difference is in the use of unicode in text fields in A2K, which can double the size of the database. A2K databases simply MUST be compacted regularly to keep them from getting entirely out of hand.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text vs Numeric Field

    'Rules of Thumb' sounds like the title for a great book.

    Or is there perhaps a website with this kind of info?

Posting Permissions

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