6

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?

Improve this question
3
  • Which column is the clustered index? ID?
    – gbn
    Aug 18, 2011 at 6:05
  • Yes, the ID column is a clustered index.
    – Rkaede
    Aug 18, 2011 at 11:43
  • Are you on sql 2000 compatibility?
    – Thiago Dantas
    Aug 18, 2011 at 21:35

3 Answers 3

Reset to default
4

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.

Improve this answer
3
  • I can confirm that it's not just a warning and that it is stopping the row from being inserted. I have tried to execute an INSERT query with more than 8060 bytes through Management Studio and received the following error: Msg 511, Level 16, State 1, Line 1 Cannot create a row of size 22689 which is greater than the allowable maximum of 8060. The statement has been terminated.. The table could definitely be normalized but there are some technical & business constraints that are not allowing this.
    – Rkaede
    Aug 18, 2011 at 11:37
  • Does the table have a clustered index on it? Is it the ID column?
    – mrdenny
    Aug 18, 2011 at 20:31
  • I had initially thought that InfoPath could not interact with related tables. It turns out that it can. So to resolve the problem I normalized the database as mrdenny had suggested.
    – Rkaede
    Aug 26, 2011 at 15:06
1

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.

Improve this answer
2
  • All 3 articles that you posted indicate that the query should be possible. Would you be able to elaborate on your answer?
    – Rkaede
    Aug 18, 2011 at 12:00
  • @cmcg Set the sp_tableoption stored procedure 'large value types out of row' option to ON to store the field off page.
    – garik
    Aug 31, 2011 at 23:27
0

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?

Improve this answer
3
  • I put in the wrong error message in question. I have updated this. My apologies.
    – Rkaede
    Aug 17, 2011 at 17:07
  • That's odd. What compatibility level is the database running under?
    – Derek
    Aug 17, 2011 at 17:35
  • SQL Server 2005 (90)
    – Rkaede
    Aug 17, 2011 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.