2

Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect data type since you would never be performing mathematical operations on them.

What is appropriate data type for these types of identifiers?

Improve this question

2 Answers 2

Reset to default
4

One of the biggest advantages to using an INT over say a CHAR or VARCHAR(10) is size. The INT value will be 4 bytes and the CHAR/VARCHAR value will be 10 bytes. NCHAR/NVARCHAR would be even larger at 20 bytes. Because of this indexes on INT values are smaller and thus faster, sometimes considerably so, than a corrisponding CHAR/VARCHAR index. Also your row sizes are smaller requiring less reads per row. This is all aside from actual space storage of course. Thomas LaRock has written several blogs on "right sizing" your data types. Does this datatype make my column look fat and How to: Right sizing the datatypes currently in the buffer pool for example.

All of this of course assumes that you in fact will not be using characters in your tracking codes. If you are then of course you have no choice but to use CHAR\VARCHAR.

Improve this answer
2

One of the problems with int is: they can't start with a "0". So whenever there is a risc of a starting 0 (postal zipcode, pin, ...), then don't use int.

Improve this answer
4
  • I have to disagree with this being considered a risk. This leading zero problem can easily be solved with formatting. To use your own zip code example, I live in Puerto Rico and our zip codes are the perfect example because they start with "00". If you really wanted to store my zip code "00918" you could store it as a 918 int and present it to the end user padded with zeroes up to 5 chars.
    – AbeyMarquez
    Apr 10, 2018 at 23:44
  • Every usage of the zip code (input, output, print on a label, print on a letter) would need to convert it to the right code, hopefully restoring the original value. I would see no good reason for this. We have codes like 06108 and 50188 in Germany, I would always suggest string for it.
    – flaschenpost
    Apr 15, 2018 at 17:08
  • 1
    @AbeyMarquez Imagine your backend sends this information to a client app. How client app would understand how many zeros it should pad the zip code? The best case would be sending yet another field that holds info about the count of "padding" zeros which wouldn't be smart choice
    – Farid
    Apr 5, 2021 at 12:23
  • @Farid If the client knows it's pulling a zip code, it knows how long it should be. Same with an account or social security number. The formatting is just a minor inconvenience. Don't get me wrong, I would also use string for pretty much any non-quantitative (descriptive) number. What I was pointing out was that there is little risk, just a bit more work on the presentation end, and a lot to gain on performance and space (as stated in Kenneth Fisher's answer), if you really need it. I've used it both ways and it's just a matter of preference and scenario.
    – AbeyMarquez
    Apr 6, 2021 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.