Sign in to follow this  

Database: int vs char()

This topic is 2842 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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 ;).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Quote:
Original post by implicit
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.
It 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).

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).

Share this post


Link to post
Share on other sites

This topic is 2842 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this