Why is Row Overflow not working on SQL Server 2005

Question

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an InfoPath form as the front end) with more than 8000 characters the SQL server returns the following error:

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

From what I understand this should not be happening due to Row Overflow.

Can anyone please provide some insight as to why this is happening?

Answer 1

This is just a warning message. You can verify that rows are overflowing to the ROW_OVERFLOW_DATA pages by using the undocumented command DBCC IND.

Personally this sounds like a table which could stand some normalization.

Answer 2

In SQL 2005, surpassing the 8,060 row size limit may impact performance. This is because SQL Server still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, or sql_variant columns exceeds this limit, SQL Server moves the record column with the largest width to another page, while maintaining a 24-byte pointer on the original page.

Moving large records to another page happens dynamically as records are lengthened based on update operations. Update operations that shorten records may result in records moved back to the original page. In addition, querying and performing other select operations such as sorts or joins on large records that contain row-overflow data slows processing time.

more...

Row-Overflow Data Exceeding 8 KB

Maximum Capacity Specifications for SQL Server 2005

ADDED:

Set the sp_tableoption stored procedure 'large value types out of row' option to ON to store the field off page.

Answer 3

It's a warning, not an error. As long as the row is within the 2GB (I believe) limit, it should have inserted. Can you verify that it was indeed inserted?