SQL Internals Reading Data Records Part 2: Null Bitmap Offset

Hello Dear Reader as I’m writing this the remnants of Tropical Storm Debby have finally blown off the coast of Florida and the sun is starting to shine.  The kids are so happy to see the blue skies that they are only out done by the dogs, whom I discovered are not fans of ran whatsoever. This is the first tropical storm we’ve had since we moved to FL, and the earliest one on record for hurricane season.  We’ll have to see if this is an omen of things to come or if it was just a happy accident that gave the state some much needed rain.

But enough of the rain and the Sunshine you stopped by for some talk about SQL Internals and that is just what we are going to get to.

NULL BITMAP OFFSET

When last we met we were discussing how to read the Tag Bytes of a Data Record.  As you will recall I posted the following picture of a Data Record from Paul Randal (@PaulRandal | Blog) and the MCM Video series on Data Structures.  I’ve updated it to point to our next topic of discussion the Null Bitmap Offset. 

I’ve heard this also referred to as the Fixed Data Record Length portion of the record, and the two confused me at first until I realized they were one in the same.  The purpose of these bytes are to tell us how much fixed length data is stored in the fixed length columns.  To stay consistent we will use the same example as we did in Part 1 .

So if you are missing that code here it is, first we’ll create a database and insert a record.

USE master;
Go

IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoInternals')
       BEGIN
              DROP Database demoInternals
       END

CREATE DATABASE demoInternals
GO

USE demoInternals
GO

/*
Let's create a Clustered Index
*/

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
BEGIN
       DROP TABLE dbo.myTable1
END
CREATE TABLE myTable1(
       myID INT IDENTITY(1,1)
       ,productName char(500) DEFAULT 'some product'
       ,productDescription CHAR(1000) DEFAULT 'Product Description'
       ,PRIMARY KEY CLUSTERED(myID)     
) ;   

/*
Insert one data record
*/
INSERT INTO dbo.myTable1
DEFAULT VALUES

GO

You can see that we have nothing but fixed length records in this example.  We’ll use DBCC IND and DBCC PAGE to get our values again.

DBCC IND(demoInternals, 'myTable1', 1)
GO

Remember PageType 10 is an allocation unit and we want to look at our data page so we want PageType 1.  We’ll turn on Trace Flag 3604 so we can see the DBCC PAGE output on our SSMS screen.

DBCC TRACEON(3604)
GO

DBCC PAGE('demoInternals', 1, 276, 3)
GO

I’m stripping the data out to only what is relevant for today.  The output of DBCC PAGE will have much more information on there as well.

0000000000000000:   1000e40501000000 736f6d65 2070726f 64756374  ..ä.....some product

Today we are looking just at the block in red e405, these are hexadecimal values that are group together in a two byte pair.  To read them we need to reverse them, instead of e405 we are actually looking at 0x05e4.  We’ll use our conversion web page from yesterday to see what this value is (for the tool just type in 05e4), http://easycalculation.com/hex-converter.php.

The value we get back is 1508.  If you look at our schema and add that up it seems a little off at first.  An integer is 4 bytes, plus 500 for our char, plus 1000 for our second char values, 4+500+1000=1504.  So where did the other 4 bytes come from? 

Our Tag Bytes are 2 bytes, and our Null Bitmap Offset are also 2 bytes.  Add those four in and you get 1508.  So let’s do one more example just to test this out.  We can’t use yesterday’s second example because it was the same table, we can’t use the third because all we did was add a variable length column to the table which wouldn’t show up in the fixed length portion of a record.  Looking at that example we can see that.

0000000000000000:   3000e405 01000000 736f6d65 2070726f 64756374  0.ä.....some product

So we will need to make a new table with a different value for our fixed length fields. 

Create Table fixedRecord
       ( myID int
       , mychar char(5)
       )
GO

INSERT INTO dbo.fixedRecord(myid, mychar)
VALUES(1, 'X')
GO

Now let’s find our data page.

DBCC IND(demoInternals, 'fixedRecord', 1)
GO

Remember to set our Trace Flag for 3604 on, if you open a new SSMS query for the script, and look at our data page.

DBCC TRACEON(3604)
GO

DBCC PAGE('demoInternals', 1, 282, 3)
GO

0000000000000000:   10000d00 01000000 58202020 20020000           ........X    ...

Our results are 0d00, remember to reverse these so we get the hexadecimal output of 0x000d, this actually translates down just to d which is equal to 13.

WRAP UP

I’ve enjoyed studying and learning on this topic, and judging by the number of hits on part 1 you guys did to.  Hope to see you next time Dear Reader and as always Thanks for stopping by.

Thanks,

Brad