6 ms·

There is value in what your saying. But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Use it as an index and y

There is value in what your saying. But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Use it as an index and you waste that space again, store it in a cache and you have less space for useful information, compare it with another SSN and it's slower etc. Now individually it's probably irrelevant but start doing this with several fields on a large application and you can noticeably slow things down even if the extra disk space seems cheap. PS: 95% of the time it's probably the safest option, but it's still something you should consider based on how that data fit's in with the rest of your application.

kls 15y ago
Some numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.
- Tsagadai 15y ago
  Phone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.
  - ListMistress 15y ago
    I agree, neither phone numbers nor social insurance numbers are unique. But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes. This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.
    - coolgeek 15y ago
      Indeed, Medicare numbers are usually the SSN with a letter appended
  - kls 15y ago
    Sure perhaps I should not have used the term "unique" but they are identifiers, they identify how to contact a certain individual based on a numeric sequence. Same with SS numbers they identify a living person. I would never use either as a unique key in my database but I am not a natural key person for the most part. I tend to like a uniquely generated key to serve as a surrogate for my unique primary identifier.
glhaynes 15y ago
Rather than the safest option "95% of the time", I'd say doing so is probably best "essentially all of the time except for a few unique circumstances, all of which you'll know when they happen."
bprieto 15y ago
Number types are meant to store numbers, not text. Even if you know the text will be just a subset of the alphabet (just 0-9) it is not a number. Just a couple of problems: some day in the future the field could need another character beyond 0-9. In Spain National Identity Numbers were once just numbers, but they changed and added a letter for validation. If you had stored this as a number you would need to change data types, and suffer a massive application update. Another problem: if you order a number field you get something like 1, 5, 100, 2000. But if you order a text field you would get 1, 100, 2000, 5. It's not nice to get the "number" order when you are expecting a "text" order.
jbigelow76 15y ago
Being cognizant of storage and indexing implications is important, but I would still probably go the char/varchar route; a distinction should be made between numeric data and text data that happens to be comprised only of numeric values. In addition whether talking about SSNs, product numbers, whatever, if you try and use a numeric field you run the risk of coming across a value that exceeds the upper bound of numeric type.
BrentOzar 15y ago
>> But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Plus it gets worse because people inevitably end up writing queries that parse the number with dashes, brackets, or other special delimiters. Those queries are CPU-intensive to do on the fly, and with databases like Oracle, DB2, and SQL Server charging by the CPU core, it becomes an expensive proposition as well.

kls 15y ago

Some numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.

Tsagadai 15y ago
Phone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.
- ListMistress 15y ago
  I agree, neither phone numbers nor social insurance numbers are unique. But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes. This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.
  - coolgeek 15y ago
    Indeed, Medicare numbers are usually the SSN with a letter appended
- kls 15y ago
  Sure perhaps I should not have used the term "unique" but they are identifiers, they identify how to contact a certain individual based on a numeric sequence. Same with SS numbers they identify a living person. I would never use either as a unique key in my database but I am not a natural key person for the most part. I tend to like a uniquely generated key to serve as a surrogate for my unique primary identifier.

glhaynes 15y ago

Rather than the safest option "95% of the time", I'd say doing so is probably best "essentially all of the time except for a few unique circumstances, all of which you'll know when they happen."

bprieto 15y ago

Number types are meant to store numbers, not text. Even if you know the text will be just a subset of the alphabet (just 0-9) it is not a number. Just a couple of problems: some day in the future the field could need another character beyond 0-9. In Spain National Identity Numbers were once just numbers, but they changed and added a letter for validation. If you had stored this as a number you would need to change data types, and suffer a massive application update. Another problem: if you order a number field you get something like 1, 5, 100, 2000. But if you order a text field you would get 1, 100, 2000, 5. It's not nice to get the "number" order when you are expecting a "text" order.

jbigelow76 15y ago

Being cognizant of storage and indexing implications is important, but I would still probably go the char/varchar route; a distinction should be made between numeric data and text data that happens to be comprised only of numeric values. In addition whether talking about SSNs, product numbers, whatever, if you try and use a numeric field you run the risk of coming across a value that exceeds the upper bound of numeric type.

BrentOzar 15y ago

>> But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Plus it gets worse because people inevitably end up writing queries that parse the number with dashes, brackets, or other special delimiters. Those queries are CPU-intensive to do on the fly, and with databases like Oracle, DB2, and SQL Server charging by the CPU core, it becomes an expensive proposition as well.