THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: A row with no fixed length columns

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:


USE testdb;

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!


Have fun!


Published Sunday, January 12, 2014 4:45 PM by Kalen Delaney
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



obat diabetes said:

August 1, 2018 11:16 PM

obat sinusitis said:

August 10, 2018 7:22 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:58 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:57 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:20 AM

obat benjolan di tubuh said:

August 30, 2018 7:39 PM

obat wasir said:

September 4, 2018 8:27 PM

obat katarak said:

September 16, 2018 5:46 PM

obat limfoma said:


September 23, 2018 6:50 PM

michael jones said:

Psychology is the most crucial discipline which supports other disciplines as well. Students have to write essay assignments on the several topics of psychology which are assigned by teachers. Students Assignment Help provides the services of customer psychology assignment help to the students

October 4, 2018 5:24 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement