In a perfect world, any table once designed would never undergo any modifications for the entire life of the product. However, nothing’s perfect and as they say – the only thing that’s constant is that nothing’s constant. Similarly, requirements change and so do table designs. However, there is one little aspect of SQL Server that most developers and novice administrators do not quite understand correctly.

Assume that we have the following setup:

CREATE DATABASE foo
GO

USE foo
GO
CREATE TABLE MyTbl (MyName CHAR(4030))
GO

INSERT INTO MyTbl
SELECT REPLICATE('a',4030)
GO

Let us assume that we need to modify the structure of our table:

ALTER TABLE MyTbl ALTER COLUMN MyName CHAR(4031)
GO

While the ALTER TABLE statement should work just fine, it does not. What we encounter is:

Msg 1701, Level 16, State 1, Line 2

Creating or altering table ‘MyTbl’ failed because the minimum row size would be 8068, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

So, what went wrong? We just wanted to increase one byte!

The post-mortem

Most of us have a tendency not to read error messages. Any message, and we just click “OK” to it. Similarly, if SQL throws anything in red fonts, we simply hit F5 again just in case SQL was not in the right spirits the first time around.

However, in this case, a close look at the message is the key to understanding what’s going on. Let’s start with the final sentence: This exceeds the maximum allowable table row size of 8060 bytes.

What is this number? 8060 bytes? It is the page size of a data page.

Per Books On Line (Understanding Pages & Extents), here’s how the numbers work out:

Data Page length in bytes	8192
Page Header	96
Overhead	36
Total Data space available on page = data page length – header length – overhead	8060

Once we have established this, let’s study what we are trying to do:

	Column properties	Data type length (bytes)	Current Row size in bytes
Current	4030	1	4030
New	4031	1	4031
TOTAL			8061

So, we are trying to take a row (because we only have one column, the column size is the row size) of 4030 bytes, and increase it to 4031 bytes, which should be okay because it ultimately remains under the 8060 byte limit. But that’s where looks are deceiving.

The ALTER Table statement attempts to expand the column “in-place”, i.e. it copies the data over to a new column with the new length specifications on the same row, making the row total now 8061 bytes, which is more than the 8060 maximum.

Now that we know we have a row that’s larger than the maximum number of bytes a page can hold, we move down the Books On Line article to see a section for Large Row support, which mentions – “Rows cannot span pages, however portions of the row may be moved off the row’s page so that the row can actually be very large.”

Rows cannot span pages – everything boils down to this simple statement.Because our request causes the row to exceed 8060 bytes “in-transit”, the ALTER TABLE fails.

You can get more information on Rows exceeding the 8K limit here: http://msdn.microsoft.com/en-us/library/ms186981.aspx

Workarounds

There are a couple of workarounds to this problem. However, all are very crude mechanisms with their own pros & cons and must not be used unless absolutely necessary.

1. The most obvious one – if possible, reduce the data within a row so that we do not exceed the allowed limits
2. Create a new table with the new specifications, copy all the data over, transforming it on the way. Finally, drop the old table
3. Export the existing data to another table/s, truncate the existing table. Make necessary changes and import data back into the original table

I hope that you found this behaviour interesting and also enjoyed learning about the internal workings of Microsoft SQL Server as much as I did.

Until we meet next time,

Be courteous. Drive responsibly.