3

I'm getting this error when I alter table alter column from nvarchar(4000) to nvarchar(max):

Cannot create a row of size 8064 which is greater than the allowable maximum of 8060

I've looked through the similar questions and I still can't explain why this doesn't work

Converting nvarchar(4000) to nvarchar(max)

Cannot create a row of size 8064 which is greater than the allowable row size of 8060

I've also tried replacing the alter statement to an add column and 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 I still get the same error.

How can I update this column and what's happening here?

Community
  • 1
  • 1
Chris Simpson
  • 7,821
  • 10
  • 48
  • 68
  • What is the overall side of the table with other columns? – Mitchel Sellers Nov 15 '10 at 22:02
  • @Mitchel - int (4), char (1), varchar (92), char (36), datetime (8), int (4), nvarchar (-1), int (4), nvarchar (4000) The last one is the one that won't convert. Strangely enough you will notice the other nvarchar(-1) which converted ok – Chris Simpson Nov 15 '10 at 22:05
  • Just to test, can you convert nvarchar to ntext? – stealthyninja Nov 15 '10 at 22:36
  • @stealthyninja - thanks for the input but this is a pretty large table and once I've done that update I'd have to restore to go back. I'll certainly test this if you can tell me how this might help or what it might prove. – Chris Simpson Nov 15 '10 at 22:55

1 Answers1

1

It's probably that the addition of the new column requires 4 bytes. It's a nvarchar(max), so the bulk of its data will be stored outside the row, but it still needs 4 bytes to declare its presence.

You could look at the largest rows:

select top 10 * from myTable order by len(myColumn_old) desc

and see if you can chop a few bytes off. Another option is to create a new table entirely which only contains the nvarchar(max) column, and then copy the data over:

insert into NewTable (col1, col2, ...) select col1, col2, ... from OldTable
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • That makes sense for the update but for the alter are you saying that sql server does an alter column by a similar add and remove process? – Chris Simpson Nov 15 '10 at 22:37
  • @Chris Simpson: All rows contain a null bitmap. So adding a `null` column can take no space, but updating that column to a string with out-of-row data can require extra bytes. – Andomar Nov 15 '10 at 23:14
  • Sorry, I probably wasn't clear then. If I have an nvarchar(4000) and I alter that to an nvarchar(max), does sql create a new column? – Chris Simpson Nov 15 '10 at 23:47
  • @Chris Simpson: Looks like altering a column is implementied by adding a column, converting the old column to the new one, and then dropping the old column – Andomar Nov 16 '10 at 05:23