stankstank
asked on
Yes or No values
I have a table which will be exceptionally large over time. I have a column within this table which keeps track of whether something happend or not. It can only be yes or no. on or off. Would the datatype of 'bit' be my best bet here? My goals are a small footprint and extremely fast data access.
I am thinking 0 = no and 1= yes in this case so bit will work. Can you give me any suggestions?
Thank you,
Stank
I am thinking 0 = no and 1= yes in this case so bit will work. Can you give me any suggestions?
Thank you,
Stank
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
note that having 1 single bit field does not provide any gain over using smallint...
Hi stankstank,
A bit field can store 3 values , 0 1 and NULL
In your application, you need only 2 values to be stored, a bit field is ery much suited for this
If you change the field to bit, you need to modify the queries in which these fields are refering, u have to use a case staement to avoi the modifications at the front end(remember your application still needs yes / no to be returned )
In order to change the column
Cheers!
A bit field can store 3 values , 0 1 and NULL
In your application, you need only 2 values to be stored, a bit field is ery much suited for this
If you change the field to bit, you need to modify the queries in which these fields are refering, u have to use a case staement to avoi the modifications at the front end(remember your application still needs yes / no to be returned )
In order to change the column
Cheers!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>A bit field can store 3 values , 0 1 and NULL
I have to disagree on that.
AFAIK: only a VARIABLE of type bit can have 3 values, while the table column cannot be null
I have to disagree on that.
AFAIK: only a VARIABLE of type bit can have 3 values, while the table column cannot be null
ASKER
>> note that having 1 single bit field does not provide any gain over using smallint... <<
I am assuming Smallint takes up more space than TinyInt because TinyInt only holds up to 255 (i think) numbers. Am I correct? If so, why wouldn't I use TinyInt instead of SmallInt? Maybe you were just giving an example - sorry if I am reading too deeply into this.
>> If you change the field to bit, you need to modify the queries in which these fields are refering, u have to use a case staement to avoi the modifications at the front end(remember your application still needs yes / no to be returned ) <<
I hate the idea of modifying queries... The groupby option may be needed. in terms of a select statement, there won't be any speed difference (if I interpreted angelIII correctly) so maybe my best bet would be a TinyInt. Correct?
The only question with using a TinyInt vs. a bit datatype (other than the groupby limitations) is how much space it will take up. Lets say - worst scenario - I had a billion or so rows. Would a TinyInt value take up much space here? A few MB? I am having troubles finding detailed info online about sql server 2005 datatypes.
What would you do in this case?
Thanks,
StankStank
I am assuming Smallint takes up more space than TinyInt because TinyInt only holds up to 255 (i think) numbers. Am I correct? If so, why wouldn't I use TinyInt instead of SmallInt? Maybe you were just giving an example - sorry if I am reading too deeply into this.
>> If you change the field to bit, you need to modify the queries in which these fields are refering, u have to use a case staement to avoi the modifications at the front end(remember your application still needs yes / no to be returned ) <<
I hate the idea of modifying queries... The groupby option may be needed. in terms of a select statement, there won't be any speed difference (if I interpreted angelIII correctly) so maybe my best bet would be a TinyInt. Correct?
The only question with using a TinyInt vs. a bit datatype (other than the groupby limitations) is how much space it will take up. Lets say - worst scenario - I had a billion or so rows. Would a TinyInt value take up much space here? A few MB? I am having troubles finding detailed info online about sql server 2005 datatypes.
What would you do in this case?
Thanks,
StankStank
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sweet. That's exactly what I needed (and wanted) to hear. I am closing this question, thanks to both of you for helping..
If you have any links available for sql server 2005 datatypes, please feel free to post. I still can't find one!
If you have any links available for sql server 2005 datatypes, please feel free to post. I still can't find one!
Whoa... 1 to 8 bit fields are stored in a byte. So whether there is one or 8, it takes up 1 byte. If there are nine bit fields it takes up two bytes.
So if you ever add another bit field you're still taking up 1 byte (if you only have two). But if you add another tinyint it takes up two bytes... etc.
Also, Angel, a bit field can store 3 values, 1, 0 and null, I assure you.
So if you ever add another bit field you're still taking up 1 byte (if you only have two). But if you add another tinyint it takes up two bytes... etc.
Also, Angel, a bit field can store 3 values, 1, 0 and null, I assure you.
declare @bit bit
set @bit = 1
SELECT @bit
set @bit = 0
SELECT @bit
set @bit = NULL
SELECT @bit
set @bit = 1
SELECT @bit
set @bit = 0
SELECT @bit
set @bit = NULL
SELECT @bit
very probably, yes.
the bit data type has one limitation which you will have to workaround: you cannot GROUP BY that bit data type itself.
workarounds:
GROUP BY CASE WHEN bit_field = 1 THEN 1 ELSE 0 END
GROUP BY CAST(bit_field AS INT )