Today I will return to my roots and post about the bits and bytes of internal storage. I received a request a while back to help with deciphering the DBCC PAGE output for a row with no fixed length columns. The person had read the section in my book (SQL Server 2008 Internals) on row storage, but was still having trouble. He presented this table with one row:
CREATE TABLE varchk(name varchar(10))
INSERT INTO varchk VALUES ('kalen')
He used DBCC IND to find the page number:
DBCC IND (testdb, varchk, 1)
and then then took the file and page number returned to pass to DBCC PAGE:
DBCC PAGE (testdb, 1, 188, 1)
He saw these bytes for the row:
30000400 01000001 0010006b 616c656e †0..........kalen
These were his questions:
How to find the variable column offset?
How to find the end fixed length column and start of Null bitmap?
How to find the offset of each variable-length column?
And here was my reply:
Here is your row:
30000400 01000001 0010006b 616c656e, it is exactly 16 bytes long.
3000 = Status Bits
0400 = 2 byte integer for length of fixed length portion, reverse bytes because it is a single integer = 0004 = 4 bytes for everything up to last fixed length column
0100 = 2 byte integer for number of columns, reverse bytes because it is a single integer = 0001 = 1 byte
00 (shaded) = Null bitmap
0100 = 2 byte integer for number of variable length columns, reverse bytes because it is a single integer = 0001 = 1 column
1000 (shaded) = 2 byte integer for position where 1st variable length column ends, reverse bytes because it is a single integer = 0010 = 16, which is last column where variable length column ends
6b616c656e = variable length data
6b = k
61 = a
6c = l
65 = e
6e = n
Your question said you were looking for the offset of the variable length column. Please reread the section (in the book) that describes the storage of rows with variable length columns. We are storing the ending POSITION, not the offset, and I say this:
A data row that has any variable-length columns has a column offset array in the data row with a 2-byte entry for each non-NULL variable-length column, indicating the position within the row where the column ends. (The terms offset and position aren’t exactly interchangeable. Offset is 0-based, and position is 1-based. A byte at an offset of 7 is in the eighth byte position in the row.)
I know that’s pretty geeky, but for anyone else struggling with understanding row storage, maybe all you needed was just one more example!