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

Question

I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. This is happening for a table having lots of varchar 250 to 500, numerics and datetime columns. I was trying to reproduce this same scenario in my test environment but unable to do so.

Do you guys have any idea how to reproduce it in Test environment? Any examples would be great for explanation.

My environment is SQL server 2005 Ent. Edition. My table has ~470 columns. I know its a bad design but its not in my control.

I tried below but it doesn't work:

create table test (col1 varchar(5000), col2 varchar(5000))   
insert into test values (replicate('A', 5000), replicate('B', 5000))

Even though record length is greater than 8060, it inserts successfully.

Answer 1

SQL Server has a limitation of 8060 bytes per row. See "Bytes per row" in Maximum Capacity Specifications for SQL Server. Additional information for this line has this phrase:

SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online.

So, when you insert two varchar values each 5000 bytes long in one row, one or both of them would be stored off-row and occupy only 24 bytes instead of 5000+2.

But, if you have 470 varchar columns in a table, you can insert only 8060/24=335 non-null varchar values in the same row of such table. In fact, even slightly less than 335, because there are other structures in a row that take some space.

MSDN: Row-Overflow Data Exceeding 8 KB

So, to reproduce it in test environment, create a table with at least 336 varchar columns and try to insert 336 non-null values that are longer than 24 characters in the same row.

Answer 2

This is due to the fact you reached the row size limitation. But this should affect your production server too?

Are you sure that both server are the same and both server use the same table design?

You can try to use nvarchar(max) instead of (e.g). nvarchar(250) for testing purposes. This will use more space and will be slower, but will avoid the 8060 limit. It should be ok for testing purposes.