Reputation: 474
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:
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.
Upvotes: 7
Views: 20090
Reputation: 153
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;
Upvotes: 3
Reputation: 474
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
Upvotes: 4