sql server - sql 2005 - nvarchar(max) - Cannot create a row of size 8064 which is greater than the allowable maximum of 8060 -

i'm getting error when alter table alter column nvarchar(4000) nvarchar(max):

cannot create row of size 8064 greater allowable maximum of 8060

i've looked through similar questions , still can't explain why doesn't work

converting nvarchar(4000) nvarchar(max)

cannot create row of size 8064 greater allowable row size of 8060

i've tried replacing alter statement add column , update:

before:

alter table mytable alter column mycolumn nvarchar(max) 

after:

exec sp_rename 'dbo.mytable.mycolumn', 'mycolumn_old', 'column' go alter table mytable add mycolumn nvarchar(max) go  update mytable set mycolumn = mycolumn_old 

but still same error.

how can update column , what's happening here?

it's addition of new column requires 4 bytes. it's nvarchar(max), bulk of data stored outside row, still needs 4 bytes declare presence.

you @ largest rows:

select top 10 * mytable order len(mycolumn_old) desc 

and see if can chop few bytes off. option create new table entirely contains nvarchar(max) column, , copy data over:

insert newtable (col1, col2, ...) select col1, col2, ... oldtable