VARCHAR2 vs NUMBER as Datatype

Posted on 2011-10-05
Last Modified: 2012-05-12
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.  
Question by:ktylerconk
    LVL 34

    Assisted Solution

    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.
    LVL 73

    Expert Comment

    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
    LVL 51

    Expert Comment

    if i see that table, I run this code below immediately:

    alter myTable
    modify ssn varchar2(6);

    :) and tell developers fix their app...

    LVL 15

    Expert Comment

    by:Franck Pachot

    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.

    LVL 31

    Expert Comment

    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.
    LVL 34

    Accepted Solution

    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.
    LVL 3

    Expert Comment

         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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >> Instead of creating new table, ...

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

    #3 is what you posted minus the FK pieces.
    LVL 31

    Expert Comment

    See attached.
    LVL 3

    Expert Comment

     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....

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    jobsrunning 3 55
    display data from previous rows 5 57
    Oracle 12c 10 83
    update set column values in oracle 3 32
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now