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: Maximum Number of Row-overflow columns

 

I received a question from one of my readers that concerned this paragraph in Inside SQL Server 2005: The Storage Engine.

The number of large variable-length columns that a table can have is not
unlimited, although it is quite large. There is a limit of 1,024 columns
in any table, so that is definitely a limit there. But another limit will
be reached before that. When a column has to be moved off a regular page
onto a row-overflow page, SQL Server keeps a pointer to the row-overflow
information as part of the original row. The pointer is always 24 bytes,
and the row still needs 2 bytes in the row for each variable-length
column, whether or not the variable-length column is stored in the row. So
it turns out that 308 is the maximum number of overflowing columns we can
have, and such a row needs 8,008 bytes just for the 26 overhead bytes for
each overflowing column in the row.

Here is the question: I understood that 26 bytes overhead is required for each overflowing column
in the row but not able to understand how you calculated 308 figure. Please explain.

Ok, I will.

In SQL Server 2005, the maximum number of bytes for any row, including the constant overhead bytes, is 8060. (A row might actually have 14 additional overhead bytes if it has been modified under one of the snapshot-based isolation levels, and those 14 bytes increase the maximum to 8074.) In addition to the bytes mentioned above, every row needs a few other overhead bytes:

2 bytes: row status information
2 bytes: length of the fixed length portion of the row
2 bytes: number of columns
1 bit per column: NULL bitmap (rounded up to whole number of bytes)
2 bytes: row-offset location (stored at the end of the page)
2 bytes: number of variable length columns (only IF there are any, otherwise these bytes aren't used)

If we subtract 11 bytes from 8060 and divide by the 26 bytes each row-overflow column needs, the result is 309 and a fraction. However, if we actually had 309 columns in the table, each would need a bit in the bitmap and 309 bits needs 39 bytes.

So for 309 overflowing columns, we would need 309 *26 + 10 overhead bytes + 39 bytes for the NULL bitmap, which is 8083 bytes, and is longer than the maximum row size.

For 308 overflowing columns, we would need 308 *26 + 10 overhead bytes + 39 bytes, which is 8057 bytes, and that will fit in a single row.

More details can be found in Inside SQL Server 2005: The Storage Engine.

Have fun!

Published Monday, July 16, 2007 4:45 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

 

Kalen Delaney said:

SQL Server 2005 allows rows to include variable length columns which do not actually fit in the maximum

October 9, 2007 2:51 PM
 

rakesh said:

When i created a table with 1024 columns and with variable length of 8000 for each column, the table is accepting 304 columns data. And with variable length of 20, the table is accepting 360 columns data. Could you please explain why there is a change in the number of columns when the length is changed.

August 19, 2008 3:02 AM
 

Kalen Delaney said:

Hi Rakesh

What you have set for the maximum length is almost irrelevant. Even though SQL Server wouldn't allow more than 304 or 360 columns, you might have received errors on INSERT if you tried to make the columns too long.

As I described in the article, each row overflow column needs a fixed 26 bytes to point to the row on the other page. So that seriously limits the total number of row overflow columns. Variable length columns that are limited to 20 can never be row overflow. So all SQL Server needs for overhead is the 2 bytes in the variable length offset array. So we can have more of them.

Best regards,

Kalen

August 19, 2008 1:15 PM
 

hung said:

follow up:

I only want to focus on the previous question that belong to rakesh.

the table is accepting 304 columns data

Why the table did not accept 308 like you said Kalen?

January 30, 2010 7:47 AM
 

Kalen Delaney : Geek City: More About Row Overflow Columns said:

January 30, 2010 5:40 PM
 

Andre said:

Hi Kalen,

I too would like you to follow up on Rakesh's query about only getting 304 columns and not the 308 you calculated. 304 x 26 = 7904 + 10 overhead and 38 NULL bitmap bytes = 7952 so there is room for a few more (as you had calculated).

Also, when using varchar(20), Rakesh gets 360 which would work out to 360 x 22 = 7920 + 10 + 45 NULL bitmap bytes = 7975 which again leaves more room for a few more columns.

What is going on here? Where is the extra overhead being used up?

Thanks!

I look forward to your reply!

August 11, 2010 12:17 AM
 

Kalen Delaney said:

Hung and Andre

I have no idea why Rakesh did not get 308 columns. He didn't tell me which version he was using and he didn't show me the DDL. I just ran another test on SQL 2008 and was able to get 308 columns again.

And his question about the varchar(20), again, i don't see any DDL from him so I am not going to try to figure out why something is happening that might just be Rakesh's mistake. I was able to getr 363 columns of varchar(20). He said he only got 360. How many did YOU get?

Thanks

Kalen

August 11, 2010 11:15 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