Maximum row size exceeds the allowed maximum of 8060 bytes

Question

In run Microsoft SQL Server 2014. Upon altering a table, I get a warning about the row size.

ALTER TABLE myTable
    ALTER COLUMN aRandomColumn NVARCHAR(10);

Warning: The table "myTable" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

The table has 75 columns:

13 date
9 bit
8 int
5 decimal(18,5)
4 bigint
3 money
19 nvarchar(10)
10 nvarchar(20)
1 nvarchar(30)
1 nvarchar(40)
2 nvarchar(50)

By my calculations, the nvarchars take up 1120 bytes, the other columns 121 (not sure but it won't be much more).

How does this exceed 8060 bytes? How can I get rid of this warning?

Tried to sum all columns with isnull(datalength(myColumnName), 1) and the actual data never exceeds 600.

Found a similar question but change tracking is off for me, so it didn't help. Also cleantable, found in this question, didn't help.

However, when I copy the table, the new table does not produce this warning.

SELECT * INTO myNewTable FROM myTable;
-- (8561 row(s) affected)
ALTER TABLE myNewTable
    ATLER COLUMN aRandomColumn NVARCHAR(10);
-- Command(s) completed successfully.

score 3 · Accepted Answer · answered Dec 03 '18 at 14:02

3

Found the solution for my problem.

This question deals with the same issue, and points to the solution in another location.

This is likely due to previous alters (particularly of existing column widths) that are still reflected in the underlying page structure. Try rebuilding the table or dropping/re-creating the clustered index to reclaim that space.

What worked for me was:

ALTER TABLE myTable
    ADD CONSTRAINT pk_bigchange PRIMARY KEY (aColumnWithUniqueNonNullValues);
GO
ALTER TABLE myTable
    DROP CONSTRAINT pk_bigchange;
GO

answered Dec 03 '18 at 14:02

TacoV

424
1
5
17

1

I confirm that a simple REBUILD of the index is enough. – David Coster Feb 25 '22 at 01:38
1

I too confirm that a simple REBUILD of the clustered index is enough – ob213 Jan 18 '23 at 00:59
`ALTER TABLE [Table] REBUILD;` if directly in SQL server – Denis G. Labrecque Jun 07 '23 at 19:47

score 1 · Answer 2 · answered Jun 08 '23 at 13:16

Initially, deleting some columns from the table might not immediately resolve the problem. The reason is that deleting columns does not automatically reclaim the space they occupied in each row. Therefore, even after column deletion, the row size may still exceed the maximum allowed size.

To resolve the issue, you need to rebuild the table using the ALTER TABLE statement with the REBUILD option. This operation reorganizes the table's data pages and reclaims the space previously occupied by the deleted columns.

ALTER TABLE [Table] REBUILD;

Maximum row size exceeds the allowed maximum of 8060 bytes

2 Answers2

Linked