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?

  • 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
up vote 17 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

  • 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)

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!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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