3

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 NCHARColumn2 to 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 NCHARColumn2 is 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.

2
  • I can't answer why the alter errors & create succeeds. But are you really using fixed-length data types up to 8kb? If you have variable-length data, and use nvarchar(), this isn't a problem.
    – AMtwo
    Mar 2, 2022 at 20:09
  • I appreciate and understand your suggestion, however I'm at the mercy of what an application is allowing me to do/support. The great plight we all eventually face. Mar 2, 2022 at 20:37

1 Answer 1

0

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 Row-Overflow Considerations is here for more information.

4
  • You seem to be missing the point. The OP can't increase the size of the last column from 10 to 20, but can create a new table where the last column is 20 from the beginning. The end result is the same but the OP can only achieve it by creating a new table rather than by increasing the size of a column in the old table.
    – Andriy M
    Mar 2, 2022 at 22:35
  • @AndriyM The quoted text in my answer is about what happens when creating a table and not altering them. Mar 3, 2022 at 17:26
  • But that's not what the OP wants to know. "I'd like to know more about why there is a difference in behavior" – asks the OP. Obviously, in order to know the difference between two possibilities, it's not enough to know the behaviour of just one of them and not the other. Also, I don't believe mentioning how varchar, nvarchar, varbinary, sql_variant, or CLR types are stored is relevant to the OP's specific scenario. You've quoted from the manual, and that information is correct, but it just doesn't help answer the question.
    – Andriy M
    Mar 4, 2022 at 1:17
  • @AndriyM The first paragraph is the behavior of altering and then the behavior of creating. Sorry if I did not describe in a good manner. The quote is not manual it is from the link at the bottom. Mar 4, 2022 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.