sql server - Cannot create a row of size 8937 which is greater than the allowable maximum of 8060

Question

We are getting this error on a table in our database:

Cannot create a row of size 8937 which is greater than the allowable maximum of 8060.

The table consists of about 400 varchar(max) fields. We are, however, only inserting empty strings into these fields.

The insert seems to work, however when using SqlXml to read the data or when running DBCC DBREINDEX on the primary key of the table, the error occurs.

It is only occurring on one particular SQL Server (2005) and not on others (2005 Express). The problem machine is running 64-bit Windows and the others are running 32-bit windows.

Has anyone got any ideas about this? Please let me know if I need to include any more information.

I'd like to point out that I completely agree that it is rather extreme, unusual and not at all sensible to be attempting to use this many varchar(max) columns. There are reasons for it, mainly not under my control, that I will not go into here.

Answer 1

The error is caused because you cannot have a row in SQL server which is larger than 8KB (the size of 1 page) because rows are not allowed to span pages - its a basic limit of SQL Server, you can read more about it here:

• Database Basics Quick Note - The difference in Varchar and Nvarchar data types

Note that SQL server will allow you to create the table, however if you try to actually insert any data which spans multiple pages then it will give the above error.

Of course this doesn't quite add up, because if the above was the whole truth then single VARCHAR(8000) column would fill a row in a table! (This used to be the case). SQL Server 2005 got around this limitation by allowing certain data from a row to be stored in another page, and instead leaving a 24-byte pointer instead. You can read about this here:

• How Sql Server 2005 bypasses the 8KB row size limitation
• Maximum Row Size in SQL Server 2005 to the Limit

As you can see this now means that rows can now span multiple pages, however single column rows still need to fit into a single page (hence the maximum size of a column being VARCHAR(8000)) and there is still a limit on the total number of such columns you can have (around 8000 / 24 = ~300 by my estimate)

Of course this is all missing the main point, which is that 400 wide columns on a single table is absurd!!!

You should take a long hard look at your database schema and come up with something more reasonable - you could start with choosing some more conservative estimates on column sizes (like VARCHAR(255) or VARCHAR(50)), but you really need to split some of those fields out into separate tables.