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?

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 02:06

score 17 · Accepted Answer · answered Feb 22 '12 at 16:55

17

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

answered Feb 22 '12 at 16:55

SQLRockstar

6,359
26
48

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

Martin Smith · Answer 2 · 2012-02-22T17:00:22.557

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)

JNK · Answer 3 · 2012-02-22T17:42:32.383

9

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!

edited Feb 22 '12 at 17:42

answered Feb 22 '12 at 16:55

JNK

17,956
5
59
97

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

3 Answers3