Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.

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:

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:

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.

The row size is determined by the types of the columns, not how much data you store in them.

Have 400 varchar fields in a single table tells me you are doing something wrong. Perhaps you need to normalize the schema?

I have this problem today. but this table's column is small and only hundreds rows. and this table work good before. I cost many time to research.

finally, I backup this table data. I delete the table. and create same new table; then restore data. everything is ok.

It shows the old table maybe crashed inside. maybe SQL server bug. anyway, issue is gone. hope save others time.

backgroud: MS SQL 2012