SQL Myth: The NULL bitmap is only added to a record when it’s got nullable columns

As seen in my previous post SQL How To: Dump the contents of a SQL Page, I mention right at the end that we have a null bitmap, yet our table did not contain any nullable columns.

We can prove this further by creating a variant of our original table with a nullable column, and another with the same column non-nullable to see the difference.

CREATE TABLE PersonWithNullable(
FirstName char(40),
LastName char(40),
NickName char(40) NULL,
Age int )

INSERT INTO PersonWithNullable VALUES ('Giles','Middleton', NULL, 40 )

EXEC sp_AllocationMetadata 'PersonWithNullable'

Results in:

And when we inspect the page null bitmap, it yeilds (our database is called ‘test’)

DBCC TRACEON (3604);
GO

DBCC PAGE('test', 1,179,3)

Results in

 

0400  04 is actually

0004 (4 columns in little endian order) plus the null bitmap 04 (00000100)

This ‘3rd bit’ represents column 3. Indicating it is null.

 

Watching the bits go by

To demonstrate this nullable bit pattern, let us create  table of all nulls and fill it with different patterns

CREATE TABLE PersonWithAllNullable(
FirstName char(40) NULL,
LastName char(40) NULL,
NickName char(40) NULL,
Age int NULL )

INSERT INTO PersonWithAllNullable VALUES (NULL,'Middleton', 'Gilesey' ,40 )

INSERT INTO PersonWithAllNullable VALUES ('Bob',NULL, 'Bobby' ,41 )

INSERT INTO PersonWithAllNullable VALUES ('Matt','Didd', NULL ,42 )

INSERT INTO PersonWithAllNullable VALUES ('Simon','Hedgeson', 'Hedgey', NULL )

After using our stored procedure and DBCC PAGE we saw…

Let’s finish off our test

To make sure I wasn’t imagining things, lets get rid of the nullable columns and see if the bit mask byte is still there.

CREATE TABLE PersonWithNoNullable(
FirstName char(40),
LastName char(40),
NickName char(40),
Age int )

INSERT INTO PersonWithNoNullable VALUES (‘Giles’,’Middleton’, ‘Gilesey’ ,40 )

After using our stored procedure and DBCC PAGE we saw…

 

Yep, an extra byte we don’t need!

 

The final twist

A table with 9 Nullable columns will cause an extra byte to be added to the row size just for the null bitmap we will not use.

CREATE TABLE NineCols(
c1 tinyint,
c2 tinyint,
c3 tinyint,
c4 tinyint,
c5 tinyint,
c6 tinyint,
c7 tinyint,
c8 tinyint,
c9 tinyint)

INSERT INTO NineCols VALUES (0xff,0xfe,0xaa,0xbb,0xcc,0xdd,0x22,0x33,0xee)

EXEC sp_AllocationMetadata ‘NineCols’

Resulted in…

 

That’s an extra byte. For our particular record, that’s quite an overhead.

So the lesson there kids, is if you are about to exceed 8, 16, 24, 32 (etc) columns, you’ll add another byte per row to your record for null bitmap storage, even if you do not use nulls.

Misconceptions around null bitmap size