 | |
NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) in Oracle10g and 9i - advantages and disadvantages
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: NUMBER(p) vs. INTEGER data type and CHAR(x) vs. VARCHAR2(x) inOracle 10g and 9i - advantages and disadvantages -
06-13-2008
, 12:08 PM
|
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? |
It goes beyond stylistic, CHAR is a pita. I was sooooo happy when the
enterprise software I work on finally converted to varchar2, there
were so many gotchas time after time after time...
jg
--
@home.com is bogus.
"...from Pasadena nudes... news about the porn trial..." - heard on
KNX news radio.
#22
| |||
| |||
|
|
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? |
It goes beyond stylistic, CHAR is a pita. I was sooooo happy when the
enterprise software I work on finally converted to varchar2, there
were so many gotchas time after time after time...
jg
--
@home.com is bogus.
"...from Pasadena nudes... news about the porn trial..." - heard on
KNX news radio.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.