Text vs Numeric Field

Topic

New Reply

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.)

Reply | Quote

Replies

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.

Reply | Quote

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

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

Reply | Quote