(For the other posts in this series, see my Misconceptions blog post category)

Today's myth is a multi-parter especially for the folks in the Designing for Performance class that Kimberly's teaching on the MS campus today - hellooooo!

The null bitmap keeps track of which columns in a record are null or not. It exists as a performance optimization to allow the Storage Engine to avoid having to read all of a record into the CPU when null columns are part of the SELECT list - thus minimizing CPU cache line invalidations (checkout this link for details of how CPU memory caches work and the MESI protocol). There are three pervasive myths to debunk here:

Myth #6a: The null bitmap isn't always present.

TRUE

The null bitmap is *always* present in data records (in heaps or the leaf-level of clustered indexes) - even if the table has no nullable columns. The null bitmap is *not* always present in index records (leaf level of nonclustered indexes, and non-leaf levels of clustered and nonclustered indexes).

Here's a simple script to prove it:

CREATE TABLE NullTest (c1 INT NOT NULL);
CREATE NONCLUSTERED INDEX NullTest_NC ON NullTest (c1);
GO
INSERT INTO NullTest VALUES (1);
GO

EXEC sp_allocationMetadata 'NullTest';
GO

You can get my sp_allocationMetadata script from my post Inside The Storage Engine: sp_AllocationMetadata - putting undocumented system catalog views to work.

Use the page IDs in the output from the script in the First Page column. Do the following:

DBCC TRACEON (3604);
DBCC PAGE (foo, 1, 152, 3); -- page ID from SP output where Index ID = 0
DBCC PAGE (foo, 1, 154, 1); -- page ID from SP output where Index ID = 2
GO

From the first DBCC PAGE dump of the heap data record:

Slot 0 Offset 0x60 Length 11

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP    
Memory Dump @0x685DC060

From the second DBCC PAGE dump of the nonclustered index record:

Slot 0, Offset 0x60, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD           Record Attributes =         <<<<<<< No null bitmap
Memory Dump @0x685DC060

Myth #6b: The null bitmap only contains bits for nullable columns.

FALSE

The null bitmap, when present, contains bits for all columns in the record, plus 'filler' bits for non-existent columns to make up complete bytes in the null bitmap. I already debunked this one with a long internals blog post last May - see Misconceptions around null bitmap size.

Myth #6c: Adding another column to the table always results in an immediate size-of-data operation.

FALSE

The only time that adding a column to a table results in a size-of-data operation (i.e. an operation that modifies every row in a table) is when the new column has a non-null default. In all other cases, the Storage Engine remembers that there are one or more additional columns that may not actually be present in the records themselves. I explained this in a little more depth in the blog post Misconceptions around adding columns to a table.

Back to a single myth-a-day for tomorrow - today was a bulk-bin special!