VARCHAR2 vs NUMBER as Datatype

I am doing a quality check on a new database, and have run into an issue: The developer made SSN and ZIP CODE NUMBER data types rather than VARCHAR2. Since many data sources from outside our company will be input to this database, I believe that the data  types should be VARCHAR2.  The developer says that NUMBER will give better performance, but when moving this data to any reports, we will have to add a function to it so that all leading zeros show, which seems like will slow down the process at that point.  Also, if an outside source sends an incorrect ZIP of only 4 characters, and we load straight into the database, we will not know if it was sent incorrectly or we just dropped a leading zero. Also, when I exported some data to a spreadsheet using TOAD, the zeros are gone, which causes a problem in my analysis software.

I am looking for an expert opinion on this, because my manager has a tendency to support the developer since he has more experience in ORACLE.  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
An interesting problem.

NUMBER datatype would in most cases be less storage and a faster search than a VARCHAR2.

However, is it possible that you would ever have addresses in Canada?  If so, then NUMBER would not work for zip as their zip codes are alpha numeric.

Also, I would suggest never loading outside data directly into your database.  It should be staged and verified before being loaded into the production tables.

In my experience, zip codes and SSNs are stored in VARCHAR2 fields.  It makes everyone's life a little easier.
number should be smaller and likely more efficient than varchar2 but, for most purposes you are unlikely to see a difference.

However,as you pointed, you will need to modify the number to reconstruct the proper data on the way out which means you'll be incurring additional work every time.  And, if any apps are written using strings, they will possibly not use indexes as they will force implicit conversion,  which then means less efficient.

And, if you are required to accept all data, including invalid data, such as the 4 digit zip codes,  then you definitely want to go with varchar2.

I can't see any defensible position for using a numeric type under that last criteria
HainKurtSr. System AnalystCommented:
if i see that table, I run this code below immediately:

alter myTable
modify ssn varchar2(6);

:) and tell developers fix their app...

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Franck PachotCommented:

The developer says that NUMBER will give better performance
This is just not true. A number may be smaller or not. And anyway, nobody will see the difference.
And even if there were a difference, the choice of the datatype do not depend on that or we would put all columns as 'RAW' datatypes...

Use NUMBER when you want do numeric operations on it: sum it, sort it numerically (meaning that 100 is greater than 11), compare it numerically (meaning that 0011 is equal to 11)... and when you want to see them right-justified.

User VARCHAR2 when you want to do character operations: substring, sort it alphabetically,, compare it with all digits, when one day one of the digit can be an alphabetical character (in some countries, zip codes have them) ... and want to see them left justified.

Just tell them to put it as VARCHAR2 or proove their claim about performance.

I wholeheartedly agree with the responses that suggest making such fields varchar2. In my work, I deal with nothing but tax returns containing SSNs, EINs and zipcodes and, occasionally, a new table might try and make such fields numeric and it causes nothing but headaches. If you're not going to do any math on a field, then don't make it a number.
Mark GeerlingsDatabase AdministratorCommented:
I agree, do not use the "NUMBER" datatype for values that you won't do arithmetic with, and especially don't use "NUMBER" if you want leading zeroes to be displayed.

You will need to modify this suggestion:

alter myTable
modify ssn varchar2(6);

to at least:

alter myTable
modify ssn varchar2(9);

But, that will work only if you plan to strip out the usual separators when data is entered, and put them back in automatically when data is displayed.

It would be simpler to do this:

alter myTable
modify ssn varchar2(11);

This will allow values like "xxx-xx-xxxx" to be stored and displayed exactly as entered.

Of course, if the table already exists and has data loaded in this column, you won't be able to change the datatype in one step.  You will then need to do one of these:

1. Create a new table with the datatype you want, then move the data to the new table, drop the orignal table, and restore the default values, indexes, contraints, grants and triggers from the original table to the new table.
2. Copy or export the records, truncate the table, change the datatype, then load the records back in.
3. Add a new column with a datatype of varchar2(11), copy the SSN data to this new column,  drop the orginal column, then rename the new column to the original name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
     Instead of creating new table, we can achieve the same by adding new column (if the column order not mater)
1. Add new column with varchar2 datatype (col1_temp)
2. Copy col1 data to col1_temp
3. Disable all the FK
4. Drop col1
5. Rename col1_temp to col1
6. Enable FK
slightwv (䄆 Netminder) Commented:
>> Instead of creating new table, ...

I would re-read markgeer's post in http:#a36926108

#3 is what you posted minus the FK pieces.
See attached.
 Sorry I miss read this " You will then need to do one of these:" as " You will then need to do these:"

anyway FK is important....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.