• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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
0
stankstank
Asked:
stankstank
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Aneesh RetnakaranDatabase 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
 
Guy Hengel [angelIII / a3]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
 
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now