Re: VARCHAR vs NUMBER

Murray Shane (smurray_at_booz.bah.com) wrote:
: Q: Is there an advantage in storing a "number" as a NUMBER data
: type given that I don't need to
: do any mathematical operations on this number field?
:
: The scenario I have is this: I want to store a case tracking number
: that is currently a NUMBER
: data type. What if this were a VARCHAR(x) data type? I could then
: use letters and numbers.
:
: Is there a performance issue on sorting, finding, etc.?
:

I can think of a few.

If you need to compare this field to a numeric field in another table, Oracle does an implicit to_char for each row of the join. I'm sure this can't help performance.

Additionally, if you end up with non-numerics in the field and try to compare it to a numeric field (i.e. in a join) it will fail with the great 'Invalid Number' error. This can be a pain to track down, especially in views.

Joe

-- 
                                   
=------------------------------------------------------------------------=
Joe Nardone               |    
joe_at_access.digex.net      |