||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.
IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoInternals')
DROP Database demoInternals
CREATE DATABASE demoInternals
Let's create a Clustered Index
IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')
DROP TABLE dbo.myTable1
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
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)
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 PAGE('demoInternals', 1, 276, 3)
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)
INSERT INTO dbo.fixedRecord(myid, mychar)
Now let’s find our data page.
DBCC IND(demoInternals, 'fixedRecord', 1)
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 PAGE('demoInternals', 1, 282, 3)
This table that we have created has a 4 byte integer field and a 5 byte char field for a total of 9 bytes. Add in our 2 bytes for our Tag Bytes and our 2 bytes for our Null bitmap offset and we should be sitting at 13 Bytes. (Once again only posting the relative portion of the DBCC PAGE output.)
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.
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.