Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which column is the clustered index? ID? –  gbn Aug 18 '11 at 6:05
    
Yes, the ID column is a clustered index. –  Rkaede Aug 18 '11 at 11:43
    
Are you on sql 2000 compatibility? –  Thiago Dantas Aug 18 '11 at 21:35
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '11 at 11:37
    
Does the table have a clustered index on it? Is it the ID column? –  mrdenny Aug 18 '11 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 '11 at 15:06
add comment

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.

share|improve this answer
    
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 '11 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 '11 at 23:27
add comment

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?

share|improve this answer
    
I put in the wrong error message in question. I have updated this. My apologies. –  Rkaede Aug 17 '11 at 17:07
    
That's odd. What compatibility level is the database running under? –  Derek Kromm Aug 17 '11 at 17:35
    
SQL Server 2005 (90) –  Rkaede Aug 17 '11 at 17:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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