NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle 10g and 9i - advantages and disadvantages

From: <dananrg_at_yahoo.com>
Date: Thu, 12 Jun 2008 16:43:32 -0700 (PDT)
Message-ID: <0df6618f-5a82-44c8-9a2e-2e535c33a6b8@25g2000hsx.googlegroups.com>


What are the main differences between NUMBER(p) and INTEGER for storing integer values? Is there any difference with space or performance?

I understand using NUMBER(p) with a value for Precision only (no scale) gives you an integer, constrained by a max of p digits. Is INTEGER the same as NUMBER(38), since 38 is the max precision?

I've also heard it said that Oracle Corp recommends, or once recommended, using the NUMBER type with no precision or scale specified; this way it accepts whatever type of number you throw at it exactly as it is. I can see how this would be bad, e.g. if you intend to hold only integers this would allow someone to enter floating point numbers. Is it only good to use NUMBER with no P + S when you expect floating point values, but don't know how many decimal places you might need?

Also, these days, is there any real advantage to using something like CHAR(5) vs. VARCHAR2(5) in terms of storage and performance? Seems with CHAR, you'll always use 5 characters regardless of what you enter (zeros padded when < 5 chars). But with VARCHAR2(5), you'll only store as many characters as are entered.

What I'm really after--is there any advantage these days to using the CHAR data type? Seems like VARCHAR2 offers more flexibility *if* the length of values in a column may increase or decrease in the future, where CHAR is best if one suspects there will always only be X characters for values in a field. So is it a stylistic thing or a domain thing in choosing one over the other? Received on Thu Jun 12 2008 - 18:43:32 CDT

Original text of this message