Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-11-11 : 05:24:21
|
| Ive been wondering this for way too long, its time to settle it in my mind :) .. I have a database designed where lots of columns are of TINYINT datatype and store either a 0, or 1 .. Example.. For storing a gender in the gender Column I store a 0 for male and 1 for female .. 0 for nonsmoker, 1 for smoker, 0 for nondrinker, 1 for drinker .. etc, etc..Is it faster doing it like this ?? Or should I store a single Char and have Y or N .. or M or FAny suggestions on speed? I know the second route is definately preferred for making sense :) thanksMike123 |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-11-11 : 05:34:56
|
| I don't think it makes a whole lot of difference. Somewhere in the back of my head I think numbers are always faster than strings but I don't know if that extrapolates down to tinyint and char(1). I've always tended to code everything having an integer key. |
 |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-11-11 : 06:11:36
|
| You could use bit also I guess. If you have <=8 bit columns in a table they will be stored together and take 1 byte of space. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-11 : 23:07:30
|
| Depending on what you are storing, and what tools you use to access your data. Bits are correct for storing Yes/No values, such as Smoker/Nonsmoker, and ADO will interpret the 1/0 as a True/False value instead of as a number. Therefore, it is incorrect for Male/Female, because you will get a True/False out. Between tinyint and char(1) for male and female, there is no space difference, both are one byte unless you use NChar, which is two bytes. As far as intuitive goes, it definitely makes more sense to store an F/M than a number, besides for the fact that a tinyint is up to 255 and you only need 2 possible values.Sarah Berger MCSD |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-11-12 : 03:00:46
|
| Or you could name the field "isfemale", then true/false makes sense. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-12 : 10:51:39
|
| Yes, but how about Unknown? The field may not have gotten filled out correctly, and Nulls in bits are a pain.Sarah Berger MCSD |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-11-13 : 01:09:23
|
| thanks for all the helpso basically speed is not really an issue?? its just about what makes more sense ?? does being the same column width mean the data is accessed at the same speed?? ThanksMike |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-11-13 : 03:51:55
|
| I think you can safely choose the option you want. You shouldn't get any problem with performance regardless. |
|
|
|
|
|
|
|
|
tinyint vs char