How SQL Server Packs BIT Columns Into a Single Byte

A common worry about bit columns is that a table full of yes/no flags will waste a byte on every flag. It will not. SQL Server packs bit columns together, up to eight of them sharing a single byte, and the catalog view that reports each bit as “1 byte” hides that fact rather than revealing it. This is a short, satisfying one to see on the page: we will store ten bit columns, watch eight of them collapse into one byte, and watch the ninth open a second.

This continues the byte-level storage series alongside the NULL bitmap, decimal, and where each column lives in a record. If DBCC PAGE is new to you, this older post covers the mechanics. Every byte below is from a SQL Server 2019 instance, and the script is included so you can reproduce it. Randolph West wrote his own excellent post on how SQL Server stores bit columns over at bornsql.ca, and it is well worth reading alongside this one.

What the documentation promises

Microsoft is direct about it: “If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.”^[1] In other words, each bit column is a single bit in a shared byte, and the byte count is CEILING(bit_columns / 8). Let us confirm it, because the catalog does not.

Ten bit columns, one row

An int key and ten bit columns, with an alternating pattern in the first eight and both of the last two set:

First, notice what the catalog says. Every bit column reports a max_length of 1:

Ten columns at “1” each looks like ten bytes. It is not. The max_length is a logical figure; the physical packing is another matter entirely.

The packing on the page

Here is the record from DBCC PAGE:

Look at the offsets. Columns b1 through b8 all report Offset 0x8, the same byte, differentiated only by “Bit position 0” through “Bit position 7”. Eight columns, one byte. Then b9 and b10 jump to Offset 0x9, the next byte, at bit positions 0 and 1. The ninth bit column is what opened the second byte, exactly as the documentation says.

Now read the bytes. After the four-byte id (01 00 00 00) comes 55 at offset 8. In binary 0x55 is 0101 0101, and reading it from bit 0 upward gives 1, 0, 1, 0, 1, 0, 1, 0, which is precisely the pattern we inserted into b1 through b8. The byte at offset 9 is where b9 and b10 live, only its lowest two bits carrying meaning. Ten Boolean values, two bytes.

What this means in practice

• Flags are cheap; do not pack them by hand. There is no storage reason to combine several yes/no attributes into a single integer with bit-masking. SQL Server already stores eight bit columns in the space of one, and separate bit columns are far clearer to query than flags & 4 = 4.
• The savings step in eights. Columns 1 through 8 are free after the first byte; the 9th costs a second byte that then covers columns 9 through 16. If you are counting bytes on an enormous table, the boundary to watch is every multiple of eight.
• A nullable bit still needs the NULL bitmap. The packed byte records the true/false value; whether the column is NULL is tracked separately in the NULL bitmap, like every other column.
• Column order does not change the packing. SQL Server groups the bit columns together regardless of where they sit in your CREATE TABLE. You do not need to cluster them in the definition to get the byte-sharing.

It is a small, tidy optimization, and one of the few places where the physical storage is more generous than the logical description suggests. Ten flags really do cost two bytes.

Have you ever hand-rolled a bit-masked integer to “save space” that bit columns would have saved for you? I would like to hear it. Find me on Bluesky or LinkedIn.

References

1. bit (Transact-SQL) – Microsoft Learn. The storage optimization: 8 or fewer bit columns in 1 byte, 9 to 16 in 2 bytes, and so on. ↩