8k row overflow error when updating row of size 5k

Question

I'm trying to update a target table which has one row of size 5k to a row of size 5k also.

Since it's one row it's easy to know the row actual size:

select *
from sys.dm_db_index_physical_stats(DB_ID('RODS_HSD_ES'), 
OBJECT_ID(N'TBL_BM_HSD_SUBJECT_AN_148_REPRO'), NULL, NULL, 'DETAILED')

Reproduce

Table wasn't altered since creation. don't see any reason why it should fail. Ideas?

Answer 1

The problem is related to the fact you are updating the clustering key, and the destination table happens to have a partitioning scheme¹. When SQL Server is requested to update any component of the clustering key, it must perform an UPDATE and DELETE, or a hybrid update where some of the rows are updated in-place, and some are not.

If you remove the clustered index from the destination table, you'll see the update works.

The error message, while perhaps a bit misleading, is accurate since the resulting row size during the update exceeds the maximum length.

I suggest you consider changing the structure of the table to:

• not use VARCHAR(MAX) for all those columns. If you don't actually need 2GB of characters in a single column, why define the column that way? Define the column to be the maximum size that will be realistically encountered.
• perhaps split this table into several tables where the resulting maximum row size is less than 8060 bytes. It seems you have several logical clusters of columns, such as the V_MAX_xxx, V_64_xxx, and V_512_xxx columns, etc.

To simplify your repro, you may want to eliminate the cursor, and only do the following DML operation:

UPDATE dbo.TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET
SET [sampletime]  = '2015-12-29 01:11:26.687';

The above column is one of the components of the clustering key and also the partitioning key (updating other CI key columns works fine).

With the clustered index in place, you get this error:

Msg 511, Level 16, State 1, Line 1

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

The statement has been terminated.

Without the clustered index in place, the statement succeeds.

---

¹ Interestingly, if we eliminate the partitioning from the repro, we find the update succeeds, even with the clustered index in place.

Answer 2

The update fails for broadly similar reasons to those I explained in answer to your previous question.

In this case, because you are potentially updating multiple rows where a key column of a unique index is changed*, SQL Server builds a plan that includes Split, Sort, and Collapse operators to avoid intermediate unique key violations (see this article for details).

The Sort operator thus introduced encounters an intermediate row (including internal overheads) of a width that exceeds the limit, so an error is raised. Adding an OPTION (ROBUST PLAN) hint to the update query shows this is unavoidable:

Msg 8619, Level 16, State 2, Line 681
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. Resubmit your query without the ROBUST PLAN hint.

The source/target data relationships are unclear to me from a brief look, but if you can guarantee that each update operation will affect at most one row, you can avoid the need for the Split/Sort/Collapse by adding TOP (1) to the update statement:

UPDATE TOP (1) [TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET] 
SET ...

This is a bit of a hack, though. Ideally, the update statement construction and indexes should provide enough information to the optimizer so it can see that at most one row will be updated. In particular, it is best practice to write update statements that are deterministic.

Given the odd design and lack of clarity in the question, I'm not even go to try to decipher the data relationships, or query & index changes that would be necessary to achieve this in detail.

* As Martin Smith pointed out in a comment, this would not be a problem in this particular situation if the table were not partitioned. Where the update sets the key to the same deterministic value in every row, Split/Sort/Collapse is not required, unless the table is also partitioned on that key. So, an alternative solution for this query is to not partition the table on sampletime.