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
stankstankAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
up to 8 bit fields, it take 1 byte in the table.
up to 16 bit fields, it takes 2 bytes in the table

each tinyint field will take 1 byte each.

SO, 1 single bit vs 1 single tinyint field will use the SAME amount of space in the table.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> Would the datatype of 'bit' be my best bet here?
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 )

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that having 1 single bit field does not provide any gain over using smallint...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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!
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
an example

 CREATE table ta (a int identity, b varchar(32))
set nocount on
insert into ta select 'yes'
insert into ta select 'yes'
insert into ta select 'no'
insert into ta select 'yes'
insert into ta select 'no'

insert into ta select NULL


Select * from ta

UPDATE ta
SET b = CASE(b) WHEN 'yes' THEN 1 WHEN 'No' THEN 0 End

SELECT * FROM ta

ALTER TABLE ta
ALTER COLUMN b bit
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
 
stankstankAuthor Commented:
>> 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
0
 
stankstankAuthor Commented:
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!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Atlanta_MikeCommented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @bit bit
set @bit = 1
SELECT @bit
set @bit = 0
SELECT @bit
set @bit = NULL
SELECT @bit
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.