Merge row size overflow in SQL Server - "Cannot create a row of size.."
10
votes
1
answer
2740
views
The target table to which I'm trying to merge the data has ~660 columns.
The code for the merge:
MERGE TBL_BM_HSD_SUBJECT_AN_1 AS targetTable
USING
(
SELECT *
FROM TBL_BM_HSD_SUBJECT_AN_1_STAGING
WHERE [ibi_bulk_id] in (20150520141627106) and id in(101659113)
) AS sourceTable
ON (...)
WHEN MATCHED AND ((targetTable.[sampletime]
***Cannot create a row of size 8410 which is greater than the allowable maximum row size of 8060.***
Why the second time I tried to merge the same row which already was inserted it resulted in an error. If this row exceeded maximum row size, it would expect for it not to be possible to insert it in the first place.
So I tried two things, (and succeeded!):
- Removing "WHEN NOT MATCHED" section from merge statement
- Running an update statement with the same row I tried to merge
Why does update using merge is not succeeding, while insert does, and direct update also does?
**UPDATE:**
Managed to find the actual row size - 4978. I've created a new table that has only this row, and find the row size this way:
And I still don't see something exceeding the allowed limit.
**UPDATE(2):**
Full reproduce
Made an effort that this reproduce will not require any additional auxiliary objects, and that the data will be (somewhat) obfuscated.
Tried this on several servers, from version 2012, and one from 2008, and was able to fully reproduce in all of them.

Asked by Yosi Dahari
(517 rep)
May 21, 2015, 03:43 PM
Last activity: Sep 27, 2023, 11:09 AM
Last activity: Sep 27, 2023, 11:09 AM