Drawbacks of storing an integer as a string in a d

Question

I have id values for products that I need store. Right now they are all integers, but I'm not sure if the data provider in the future will introduce letters or symbols into that mix, so I'm debating whether to store it now as integer or string.

Are there performance or other disadvantages to saving the values as strings?

Answer 1

I'm not sure how good databases are at comparing whether one string is greater than another, like it can with integers. Try a query like this:

SELECT * FROM my_table WHERE integer_as_string > '100';

Answer 2

Integers are more efficient from a storage and performance perspective. However, if there is a remote chance that alpha characters may be introduced, then you should use a string. In my opinion, the efficiency and performance benefits are likely to be negligible, whereas the time it takes to modify your code may not be.

Answer 3

1. You won't be able to do comparisons correctly. "... where x > 500" is not same as ".. where x > '500'" because "500" > "100000"
2. Performance wise string it would be a hit especially if you use indexes as integer indexes are much faster than string indexes.

On the other hand it really depends upon your situation. If you intend to store something like phone numbers or student enrollment numbers, then it makes perfect sense to use strings.

Answer 4

Do NOT consider performance. Consider meaning.

ID "numbers" are not numeric except that they are written with an alphabet of all digits.

If I have part number 12 and part number 14, what is the difference between the two? Is part number 2 or -2 meaningful? No.

Part numbers (and anything that doesn't have units of measure) are not "numeric". They're just strings of digits.

Zip codes in the US, for example. Phone numbers. Social security numbers. These are not numbers. In my town the difference between zip code 12345 and 12309 isn't the distance from my house to downtown.

Do not conflate numbers -- with units -- where sums and differences mean something with strings of digits without sums or differences.

Part ID numbers are -- properly -- strings. Not integers. They'll never be integers because they don't have sums, differences or averages.