January 4, 2009 at 7:40 pm
Hello
I am wondering, I have a column to store the number people in a town
Should this column be as int or as varchar? The value can be in a range of 999 - 99 999 999
No calculation will be made with the value of the column, it's only for an informational value purpose.
Thank you
Martin
January 4, 2009 at 7:52 pm
Nevertheless. Numbers should only be stored as numbers.
The number of people in a town is a whole number only, so it should be stored as an integer.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 4:10 am
yes it should be stored as an int in this case but should the value range change, you should be aware of the variations:
bigint
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). Storage size is 8 bytes.
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.
smallint
Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.
tinyint
Integer data from 0 through 255. Storage size is 1 byte.
James Howard
January 5, 2009 at 9:29 am
Use the numeric datatype.
1. Stated above, choose the type of numeric based on the possible range of values. Even if you guess wrong, it's fairly easy (or easy with the apps I have) to size up a numeric datatype to allow bigger numbers.
2. Stated several times in these discussion groups and worth repeating, this is just good design for defense on data quality. If it's a number, store it as a number - anything else invites bad data. Example, is my number 30, "#30", or "thirty", or something non-numeric like "?"
January 5, 2009 at 12:06 pm
dubem1 (1/4/2009)
...No calculation will be made with the value of the column...
While there may not be any requirement currently for calculations, I could easily see sorts or some kind of range grouping done by this value, which would certainly work better as an INT datatype than as VARCHAR. INT is probably the most optimal type since it's only 4 bytes in size and seems very unlikely that the population of one city will be over 2 billion.
January 5, 2009 at 12:28 pm
Go with integer. The moment you implement it as varchar, someone will come along and want to use it for calculating percentages of the population and so on.
Also, 10000 is five bytes of character data, but only 4 bytes of integer data, and 100000 is six bytes of character data and 4 bytes of integer data. Smaller database and faster IO with int, so long as a significant percentage of your data will be more than four digits. Plus, of course, varchar adds two bytes because of the variable length, so it's really if the majority of your data will be more than two digits.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2009 at 2:41 pm
If the data is numeric use a numeric type, in this case integer. As has been mentioned in other posts, as soon as you go character you get data like "2 million". It's easy to convert and format a number in the GUI, a lot easier than trying to do any kind of calculation on varchar in the database when you have invalid numeric data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply