[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

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.  
0
ktylerconk
Asked:
ktylerconk
2 Solutions
 
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.
0
 
sdstuberCommented:
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
0
 
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...

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Franck PachotCommented:
Hi,

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.

Regards,
Franck.
0
 
awking00Commented:
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.
0
 
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.
0
 
gajmpCommented:
markgeer:
     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
0
 
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.
0
 
awking00Commented:
See attached.
comment.txt
0
 
gajmpCommented:
slightwv:
 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....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now