Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was poking around SSMS and noticed the "size" of my INT columns were 4 bytes (expected) but I was a bit shocked to see my BIT columns were a whole byte.

Did I misunderstand what I was looking at?

share|improve this question
1  
I'd feel lucky that BIT columns are aligned on a byte boundary instead of a word boundary. –  Mike Sherrill 'Cat Recall' Feb 23 '12 at 2:06

3 Answers 3

up vote 9 down vote accepted

How many bit columns do you have defined in the table? I found this on MSDN, it says 8 or less bit columns are stored as one byte.

http://msdn.microsoft.com/en-us/library/ms177603.aspx

share|improve this answer
    
Accepted for MSDN source which I failed to locate. Thanks. –  Nate Feb 22 '12 at 21:19

Yes.

If you only have one bit column in the table then storage uses a byte but up to 8 bit columns can be stored in the same byte so the next 7 are "free" in that respect.

There is also a 1 bit per column storage need for the NULL_BITMAP (again rounded up to the next byte). In the data pages this contains a bit for all columns irrespective of whether or not they allow NULL (with the exception of nullable columns added later as a metadata only change via ALTER TABLE where the row has not yet been updated)

share|improve this answer

BIT uses one byte per row, but you can pack up to 8 BIT fields into that one byte storage.

So the first field costs one byte, but the next seven are free!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.