THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 TRACEON(3604)
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!

~Kalen

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

Comments

 

Bing.Com said:

Kalen Delaney : Geek City: A row with no fixed length columns

September 19, 2014 2:40 AM
 

nike free run said:

Kalen Delaney : Geek City: A row with no fixed length columns

September 19, 2014 3:11 AM
 

billiga nike free 5 said:

Kalen Delaney : Geek City: A row with no fixed length columns

September 19, 2014 10:38 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement