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:

PersonWithNullable

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
PersonWithNullablePageDump

 

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…

1FirstNameNull 2LastNameNull 3NickNameNull

4AgeNull

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…

5FinalProofnullbitmap

 

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…

6PeskyByte

 

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.

http://www.sqlskills.com/blogs/paul/misconceptions-around-null-bitmap-size/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s