Expanding existing column results in 'minimum row size would be XXXX' error, but creating a new table with the desired data length does not
I've encountered this problem a few times over the years and know how to work around it, however I am not sure why it occurs in the first place.
Let's say we create this table to start:
CREATE TABLE dbo.myTable1 ( IntCol INT ,NCHARColumn1 NCHAR(4000) ,NCHARColumn2 NCHAR(10) )
If we then attempt to expand
NCHARColumn2to 20 characters...
ALTER TABLE dbo.myTable1 ALTER COLUMN NCHARColumn2 NCHAR(20)
...we get the error:
Creating or altering table 'myTable1' failed because the minimum row size would be 8071, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
Alternatively, if we create a new table where
NCHARColumn2is already 20 characters, there is no issue:
CREATE TABLE dbo.myTable2 ( IntCol INT ,NCHARColumn1 NCHAR(4000) ,NCHARColumn2 NCHAR(20) )
As a workaround I'd just migrate the existing data into a new table with the desired data length, but I'd like to know more about why there is a difference in behavior. I appreciate any insights.
The scenario that you described is totally normal.
The maximum size of every single row is per page is 8060 bytes. myTable1 is less than the limit, so will create successfully. But when you try to make the column larger, the sum of size meets the maximum and the mentioned error occurs.
But it does not mean that a row of table can not exceeded the 8060 byte when creating a new table; Because in this case SQL can arrange the columns into multiple allocation units.
When a combination of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds this limit, the SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page
Reading the full https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186981(v=sql.105)?redirectedfrom=MSDN is here for more information.