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: Detecting Overflowing Columns

SQL Server 2005 allows rows to include variable length columns which do not actually fit in the maximum row size of 8060 bytes. I previously posted about row-overflow columns, and discussed the maximum number of such columns that you could have in a single table. You can read that post here.

Just recently I received another question that related to the section about row-overflow columns in Inside SQL Server 2005: The Storage Engine. I described all the bytes that SQL Server needs for overhead in a data row, but I didn't mention how SQL Server knows that a column in an overflowing column or not. I mentioned that if a column is stored off the row, it leaves a 24-byte pointer behind, but how does SQL Server know that the those 24 bytes are a pointer and not an actual data value?

I needed to do a little exploration to figure out the answer to that one. I'll show you some of the tests I ran to find the answer, but first I'll just give the answer, in conjunction with a review of SQL Server's data row structure. The figure below can be found in The Storage Engine book, and the following discussion is not intended to replace the vast amount of information in that book and the complete discussion of the meaning of all the overhead bytes in each row.

The bytes of interest here are the ones called "Column offset array", which are in the second to last position in the figure. If you have variable length columns, each of those columns uses two bytes to keep track of where on the row that column ends. This information has to be stored with each row, because for each row, the variable length columns can be a different length.  

Normally, a two byte value is a small int, which can represent values from
-2^15 (-32,768) to 2^15-1 (32,767). Since byte offsets on the row can only be positive, negative numbers are not needed and SQL Server uses the sign bit (i.e. the high-order bit) to indicate that the column represents a row-overflow pointer and not an actual data value.  I will show you one of the tests I ran that illustrates this. I actually ran many similar tests, and then got confirmation from someone on the SQL Server team at Microsoft that the high order bit of the column offset value is where the information is stored.

 

Validation:

First, create a table in a test database, which has the possibility of row-overflow.

USE testdb
GO
CREATE TABLE bigcols
(a int, b varchar(8000), c varchar(8000) )

Now insert one row into the table, that is big, but will not need to overflow. The first variable length column is 8000 bytes, and the second is 24 bytes.

INSERT INTO bigcols
SELECT 1, replicate('b', 8000), '123456789012345678901234'

USE DBCC IND to find the page number for the data page, and then use DBCC PAGE to look at the page.  In my testdb, DBCC IND indicated that the data page for this table was on file 1, page 1001. You will most likely get a different page number, so use that in your DBCC PAGE command.

DBCC IND (testdb, bigcols, -1)
DBCC TRACEON(3604)
DBCC PAGE (testdb, 1, 1001,1)

Here is the first part of the row on page 1001:

30000800 01000000 03006002 00511f69
1f
626262 ....

The red bytes are number of variable length columns (2), the first variable length column offset is in green, and the second column offset is in blue.

We have to reverse the bytes before translating, and there are two characters per byte, so the first column offset value is 0x1f51 or 8017. The second column offset is 0x1f69 or 8041, which is 24 bytes after the first. This is expected, as I inserted 24 bytes into the second variable length column.

I now insert another row, with a large value in the third column that will not fit on the page.

INSERT INTO bigcols
SELECT 1, replicate('b', 8000), replicate('c', 8000)

Looking at DBCC IND I see that the row-overflow page is 1002 and the new data page is 1003.  Using DBCC PAGE to look at 1002 I see that it contains the c's (the last column). I then use DBCC PAGE to look at 1003.

DBCC IND (testdb, bigcols, -1)
DBCC PAGE (testdb, 1, 1002,1)
DBCC PAGE (testdb, 1, 1003,1)

Here is the first part of the row on page 1003:

30000800 01000000 03006002 00511f69
9f
626262 ....

The only difference from the first row, with no row-overflow columns, is the offset of the second variable length column. Interpreting 0x9f69 as an unsigned integer gives us 40809, which is obviously not an offset in a row, since it is 5 times more than the number of bytes allowed in a row. So instead, convert to binary 1001111101101001 and notice the high order 1. Now change that 1 to 0 giving 0001111101101001 and convert that to decimal. It gives us 8041, again indicating that the second variable length column ends 24 bytes after the first. The only difference is that SQL Server will interpret those 24 bytes in the second row as a pointer to row-overflow data and will interpret those 24 bytes in the first row as the real data in the column.

For more information about using DBCC IND and DBCC PAGE, you can read Inside SQL Server or some of my articles in SQL Server Magazine. Or you can read the Microsoft SQL Server Storage Engine blog. Here is a post by Paul Randal, showing the use of DBCC IND and DBCC PAGE. The post explains the output of DBCC IND, and includes links to other posts that explain DBCC PAGE.

Have fun!

~Kalen

Published Tuesday, October 09, 2007 12:27 PM by Kalen Delaney

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

 

chenchangliang said:

30000800 01000000 03006002 00511f69

is "60" for null-bitmap?

July 30, 2014 5:15 AM
 

Kalen Delaney said:

Looks like it... the exact meaning of the bits, that it, which bit means which column, has changed from version to version, and I don't know what version you have.

August 5, 2014 4:31 PM
 

chenchangliang said:

exec your example sql in sql server 2008:

CREATE TABLE bigcols

(a int, b varchar(8000), c varchar(8000) )

INSERT INTO bigcols

SELECT 1, replicate('b', 8000), '123456789012345678901234'

DBCC IND (Test, bigcols, -1)

DBCC TRACEON(3604)

DBCC PAGE (Test, 1, 380,1)

the result is:

3000 0800 01000000 0300 10 0200 511f 691f 626262

if 0x10 bytes is NULL bitmap,convert to binary 00010000,but the record don't have NULL value, i don't understand it

August 6, 2014 4:59 AM
 

Kalen Delaney said:

Hi chenchangliang

All that matters is the last 3 bits, since you only have 3 columns. And the last 3 bits are all 0, indicating no nulls.

The first 5 bits are irrelevant, and in fact, if you created an identical table, it might have different values for the first 5 bits depending on what was on the page before it was used for this table 'bigcols'.

You can do more testing for yourself. Insert another row into the same table with the last column NULL and the last 3 bits should be 100. I inserted two such rows, and one had 1c for the null bitmap and the other had 3c, but the last 3 bits were the same. I inserted another row with the last 2 columns NULL and the null bitmap was 06 or 00000110.

My test was just run on SQL2008R2 because I no longer have a SQL2008 instance.

So, again, all that matters is the last 3 bits.

I hope this helps.

~Kalen

August 6, 2014 4:20 PM
 

chenchangliang said:

Thanks for such detailed explanation.

i got another two questions for your book<sql server internals 2008> on page number 376

the sample code is run on SQL2008R2:

USE test;

CREATE TABLE dbo.bigrows

(a varchar(3000),

b varchar(3000),

c varchar(3000),

d varchar(3000) );

INSERT INTO dbo.bigrows

SELECT REPLICATE('e', 2100), REPLICATE('f', 2100),

REPLICATE('g', 2100), REPLICATE('h', 2100);

DBCC IND (Test, bigrows, -1)

the result is :

In-row data on page number 5289,Row-overflow data on page number 5287

one question:

why sql server choose 'f' values on the Row-overflow page? not 'g' or 'h'?

two question:

the overhead 14 bytes in Row-overflow page is:

0800 4208 0000 db0b0000 00000300 6666

can you tell me the structure about it ?

August 7, 2014 2:41 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