VARCHAR2 vs NUMBER as Datatype
Posted on 2011-10-05
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.