The NULL Bitmap: How SQL Server Records Nullability
If you have ever run DBCC PAGE on a data page, you have seen the phrase Record Attributes = NULL_BITMAP on every single row, and it shows up even on tables where every column is declared NOT NULL. That is not a mistake, and it is not conditional. SQL Server records the nullability of a row in a small run of bits called the NULL bitmap, and understanding where it sits in the record explains a few things people get wrong about NULL: whether it is free, whether NOT NULL avoids the cost, and why a NULL in a fixed-length column does not give you the space back. This post finds the NULL bitmap on a real page and watches the bits move.
This is the record-header companion to where SQL Server stores each column, and it continues the byte-level storage series alongside decimal, money, and the uniqueidentifier. If the DBCC PAGE mechanics are new to you, this older post walks through them. Every byte below is from a SQL Server 2019 instance, and the scripts are included so you can reproduce them.
The shape of a record
Before the bitmap makes sense, here is the layout of a simple fixed-length data record, in the order the bytes appear on the page:
- Two status bytes. Bit flags describing the record. One of them, value
0x10, means “this record has a NULL bitmap”. - Two bytes: the length of the fixed-length portion. An offset pointing just past the fixed-length columns.
- The fixed-length column data. Every
int,datetime,decimal, and so on, laid out end to end. - Two bytes: the column count. How many columns the record has.
- The NULL bitmap. One bit per column, rounded up to whole bytes.
- The variable-length section, if any. Its own count and offset array, then the variable data.
The bitmap is not tacked on at the end as an afterthought; it lives right after the column count, in the body of every record. Let us confirm each claim.
Even an all-NOT-NULL table has one
The simplest possible case: three int columns, all NOT NULL, one row.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[nb_notnull] ( [a] int NOT NULL , [b] int NOT NULL , [c] int NOT NULL ); INSERT INTO [dbo].[nb_notnull] ( [a], [b], [c] ) VALUES ( 1, 2, 3 ); |
The record on the page:
|
1 2 3 |
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP 0000000000000000: 10001000 01000000 02000000 03000000 030000 |
Read it byte by byte. 10 00 is the two status bytes, and that 0x10 bit is SQL Server telling you a NULL bitmap is present. 10 00 again is 0x0010 = 16, the offset to the end of the fixed data. Then the three integers: 01 00 00 00, 02 00 00 00, 03 00 00 00 (little-endian 1, 2, 3). Then 03 00 = 3, the column count. And finally a single byte, 00: the NULL bitmap, with no bits set because nothing is NULL.
There it is. A table with no nullable columns still spent a byte on a bitmap that can only ever read zero. The presence of the bitmap has nothing to do with whether you declared the columns NULL or NOT NULL; it is part of the record format itself.
Watch the bits flip
Now four nullable int columns, and three rows that are NULL in different places:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[nb_mixed] ( [a] int NULL , [b] int NULL , [c] int NULL , [d] int NULL ); INSERT INTO [dbo].[nb_mixed] ( [a], [b], [c], [d] ) VALUES ( 10, NULL, 30, NULL ) , ( NULL, NULL, NULL, NULL ) , ( 10, 20, 30, 40 ); |
The last byte of each record is the bitmap. For four columns that is still one byte, and only the low four bits matter. Bit 0 is the first column, bit 1 the second, and so on; a bit set to 1 means that column is NULL:
|
1 2 3 4 5 |
row bitmap byte binary meaning ------------------------ ----------- ---------- ---------------------- (10, NULL, 30, NULL) 0x0A 0000 1010 columns b and d are NULL (NULL, NULL, NULL, NULL) 0x0F 0000 1111 all four are NULL (10, 20, 30, 40) 0x00 0000 0000 none are NULL |
The first row, 0x0A, is 0000 1010 in binary: bit 1 (column b) and bit 3 (column d) are set, which are exactly the two columns you passed NULL. The all-NULL row reads 0x0F, and the fully-populated row reads 0x00. The bitmap is the single source of truth for which columns are NULL; the value bytes themselves cannot tell you, which is the whole reason the bitmap has to exist.
That last point has a consequence worth pausing on. In the first row, b and d are NULL, yet they are fixed-length int columns, so the record still reserves their four bytes each inside the fixed-length region. The bytes hold leftover content and are simply ignored because the bitmap says the column is NULL. A NULL in a fixed-length column does not shrink the row. Only variable-length columns can actually reclaim space when they are NULL or empty.
How big is the bitmap?
One bit per column, rounded up to a whole byte, so the size is CEILING(column_count / 8). The interesting moment is the boundary. Eight columns fit in one byte; the ninth forces a second. Two tables, eight and nine tinyint columns:
|
1 2 3 4 5 |
eight columns (record length 15): 0000000000000000: 10000c00 01020304 05060708 080000 nine columns (record length 17): 0000000000000000: 10000d00 01020304 05060708 09090000 00 |
In the eight-column record, after the eight data bytes 01..08 comes 08 00 (column count 8) and then a single 00 bitmap byte. In the nine-column record, after 01..09 comes 09 00 (column count 9) and then 00 00, two bitmap bytes. Crossing from eight to nine columns grew the bitmap by a byte, and it will grow again at 17 columns, 25, and so on. For any realistic table this is a rounding error, but it is why the row overhead ticks up in steps rather than smoothly as you add columns.
What this means in practice
None of this is something to optimize against; the bitmap is tiny and mandatory. But a few myths dissolve once you have seen it:
- NOT NULL does not save the bitmap. The bitmap is part of the record format, present whether or not any column is nullable. Declaring columns
NOT NULLis about data integrity and giving the optimizer better information, not about trimming this byte. - A NULL in a fixed-length column is not free. The
int,datetime, ordecimalstill occupies its full fixed width in the row; the bitmap just flags it as absent. If you are storing many rows that are mostlyNULLin wide fixed columns, that space is still spent. - Variable-length columns are the ones that reclaim space. A
NULLor emptyvarcharcan genuinely take no data bytes, because the variable-length section is driven by its own offset array. That is a topic for the row-overflow and variable-length post, but it is the flip side of the fixed-length rule above. - The row has fixed overhead beyond your data. Four bytes of header plus the column count plus the bitmap ride along on every row, before a single byte of your data. It is small, but on a very narrow table with billions of rows it is real, and it is why the true on-disk row size is always a little larger than the sum of the column widths, and why that overhead counts against the 8,060-byte row limit.[1]
The NULL bitmap is one of those pieces of the engine that is completely invisible from T-SQL and completely consistent once you look at the bytes. Every row has one, it holds one bit per column, and it is the only thing on the page that knows a NULL from a zero.
Did you expect a NOT NULL table to skip the bitmap, or had you already made peace with it? I would like to hear it. Find me on Bluesky or LinkedIn.
References
- Page and Extent Architecture Guide – Microsoft Learn. The 8-KiB page, the 96-byte page header, the slot array, and the 8,060-byte row limit that includes row overhead such as the header and bitmap. ↩