storing numbers as varchar

Question

Is it ok to store numbers as varchar?

What's the difference between

int 123456789012 and varchar 123456789012 ?

Answer 1

No, it's almost always a bad idea.

• will use more space
• indexes will not perform as well
• you can't do arithmetic
• the data is not self-validating because of type
• auto-model generators will give you string type instead of numeric
• aggregates like SUM will no longer work
• the output may sort incorrectly
• you will need to CAST to use it as a number, causing performance hit
• etc.

Answer 2

You can store leading zeroes to a varchar that you can't do with integer columns (eg. it is possible to have difference between 123 and 0000123). For example zip codes in some countries. However, if you need to do that, then you are really dealing with textual information that should have varchar column. For example, phone numbers or zip codes should definitely go to a varchar column.

Otherwise if you are using your data like numbers (adding them together, comparing them, etc.) then you should put it into integer column. They consume far less space and are faster to use.