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!