Nonclustered index leaf records always contain a null bitmap – since SQL 2012

While testing a script that involved calculating index record size recently I was getting some confusing results depending on server version, and after some digging it appears there was a somewhat undocumented change to nonclustered index leaf page structure in SQL Server 2012.

Prior to 2012, as dicussed by Paul Randal in this 2010 blog post (which is still the top result for searching for ‘nonclustered index null bitmap’, hence this post) the null bitmap – that is, a >= 3 byte structure representing null fields in a record – was essentially present in all data pages but not the leaf pages of a nonclustered index that had no nulls in either the index key or any clustering key columns.

This is easy to re-demonstrate with the same simple example table from that post. Using SQL 2008R2, create the table, then pull the pages of the heap with DBCC IND;

CREATE TABLE dbo.NullTest (c1 INT NOT NULL)
CREATE NONCLUSTERED INDEX ncx_test
ON dbo.NullTest (c1)
INSERT INTO dbo.NullTest (c1)
VALUES (1)
DBCC IND ('TestDB', 'NullTest', 0)

PagePID 153 – the page at IndexLevel 0 – is the data page containing the record, so let’s look at that with DBCC PAGE – specifically, the DATA records;

DBCC PAGE ('TestDB',1,153,1) WITH TABLERESULTS

So we have a NULL_BITMAP attribute, and a record size of 11 bytes. That’s 4 bytes of header, 4 bytes for the INT, 3 bytes for the null bitmap = 11 bytes.

Now pull the pages of the nonclustered index;

DBCC IND ('TestDB', 'NullTest', 2)

This time we want to run DBCC PAGE on PagePID 155, again at IndexLevel = 0;

DBCC PAGE ('TestDB',1,155,1) WITH TABLERESULTS

Now we get an empty ‘Record Attributes’ field, and a size of 13 bytes. This makes sense – the index key itself is 4 bytes, there are 8 bytes for the data row locator (RID), 1 byte for the row header, and we don’t have those 3 bytes of null bitmap anymore.

Now let’s try the same on SQL 2012. Create the same table and index, and pull the data page;

CREATE TABLE dbo.NullTest (c1 INT NOT NULL)
CREATE NONCLUSTERED INDEX ncx_test
ON dbo.NullTest (c1)
INSERT INTO dbo.NullTest (c1)
VALUES (1)
DBCC IND ('TestDB', 'NullTest', 0)

DBCC PAGE ('TestDB',1,296,1) WITH TABLERESULTS

So far, the same.

Now for the index record;

DBCC IND ('TestDB', 'NullTest', 2)

DBCC PAGE ('TestDB',1,304,1) WITH TABLERESULTS

This time, we have a NULL_BITMAP attribute and a record size of 16. That’s 4 bytes of index key, 8 bytes of RID, 3 bytes of null bitmap and 1 byte of header.

(This is unrelated to compatibility level – running this test on a level 100 database on SQL 2012 returns the same results.)

I can’t find a link to any kind of change documentation for this, but it is subtly informed by the Microsoft documentation for estimating the size of a nonclustered index. In that document, while non-leaf records have a ‘if there are any nullable columns …‘ caveat, the formula given for leaf records is simply Leaf_Null_Bitmap = 2 + ((Num_Leaf_Cols + 7) / 8), implying it’s always there.