Does a SQL Server bit column really use a whole byte worth of space?

Question

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?

Answer 1

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

Answer 2

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)

Answer 3

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!