Use bit data type for boolean?

Question

Use bit data type for boolean?

Bilster-223920

Old Hand

Points: 321
More actions
April 15, 2005 at 8:11 am

#85532

A few things here regarding using the BIT data type for booleans.
1) Storage Space? I would guess that SL server still has to use at least a byte - or prolly more to store a "bit". This applis to disk and memory.
2) I prefer using a char(1) and setting it to 'Y' or 'N'. Everyone knows for sure, immediately, what the data means.
Does anyone know the facts the behind storage of bits?
Flame me if you want regarding my use of chars.
- B

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Yes, 1 byte is always needed to store bit columns, even if there is only one column. However, if there are more than one bit column on a table they will share that byte, so if you have eight bit columns the storage needed will still be 1 byte.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Bilster-223920 Old Hand Points: 321 More actions · Answer 2

Thanks - pretty much what I figured.

What do you recommend using for bools?

ron k SSCertifiable Points: 6103 More actions · Answer 3

For what its worth, my recommendation is to use the BIT datatype. It provides the built in limitation of valid values and when looking at the column definition it is instantly clear that its purpose is as an on/off true/false indicator.

Just my 2 cents.

noeld SSC Guru Points: 96590 More actions · Answer 4

But..

If you are planning to use many BIT columns and the table can be large guess what you can't index it it is better then to group them and use bit manipulation functions to query the indexed column(s)

* Noel