Database: int vs char()
Author
So I was in my database class today, and our teacher gave the example of having a char(5) to store a numeric string, and said it would be better to use that than an int because it would save less data.
She said that int's were 4 bytes, so it would be 6*4 for a 5 digit numeric instead of 6*2 for 5 digit numeric using char.
I asked her, because a 4 byte int should contain all the 5 digit numerals anyway, why char(5) would be better, but she continued to say it was smaller, and told me to count all the bits to get the byte size. I thought that all ints were stored as max 4 bytes standard, so now I am confused. I just don't understand why/if she's wrong or maybe I misunderstood what she was saying completely.
Is there a reason to use a char() over an int for a pk in a database?
The only way it's taking less space is if you're doing something moronic like storing the digits of a number in separate ints.
And really, most databases have separate sized ints if you're really picky.
And really, most databases have separate sized ints if you're really picky.
Does she teach MIS as the school I attended, by chance...? I swear, so much misinformation from my teachers... anyway.
Your int is 4 bytes, as you expected. Char is 1 byte per, so char(5) would be 5 bytes.
Changing things to bits doesn't change anything, since it then becomes 32 and 40 :)
I'm not sure where exactly she'd be getting 6*4 or 6*2...
In any case, if you're capping out at 5 digits anyway, a smallint would technically be your best option at 2 bytes :)
Your int is 4 bytes, as you expected. Char is 1 byte per, so char(5) would be 5 bytes.
Changing things to bits doesn't change anything, since it then becomes 32 and 40 :)
I'm not sure where exactly she'd be getting 6*4 or 6*2...
In any case, if you're capping out at 5 digits anyway, a smallint would technically be your best option at 2 bytes :)
Author
Quote:Original post by Telastyn
The only way it's taking less space is if you're doing something moronic like storing the digits of a number in separate ints.
And really, most databases have separate sized ints if you're really picky.
that's what I thought. We're using mysql, so it would be:
http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html
But thanks for the link, or I wouldn't have looked for the mysql version ;).
Author
Quote:Original post by evolutional
*slaps forehead*
Nothing like seeing teachers spread misinformation, eh?
She's foreign, so I'm giving her benefit of the doubt on me not completely understanding what she meant :-/
*pokes eyes*
And even if she would be right, these micro um, 'optimizations' have nothing to do with proper database design and maintenance.
And even if she would be right, these micro um, 'optimizations' have nothing to do with proper database design and maintenance.
How do relational databases typically deal with character-encoding anyway? I'd imagine that you often can't afford to just make everything full 16/32-bit Unicode. and I don't suppose UTF-8 works in fixed-width fields.
Quote:Original post by implicitIt depends on the engine. Most databases allow you to specify encoding on a per-column basis. Some also have specfic UTF-16/UTF-32 datatypes (e.g. NCHAR/NVARCHAR is UTF-16 in SQL Server).
How do relational databases typically deal with character-encoding anyway? I'd imagine that you often can't afford to just make everything full 16/32-bit Unicode. and I don't suppose UTF-8 works in fixed-width fields.
Storing numbers in CHAR fields is silly. Not only does it waste space, but it totally screws up collation as well (for example, "1000" would sort before "2" in text columns).
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
