Converting ntext to nvcharmax(max) - Getting around size limitation
- by Overflew
Hi all,
I'm trying to change an existing SQL NText column to nvcharmax(max), and encountering an error on the size limit. There's a large amount of existing data, some of which is more than the 8k limit, I believe.
We're looking to convert this, so that the field is searchable in LINQ.
The 2x SQL statements I've tried are:
update Table
set dataNVarChar = convert(nvarchar(max), dataNtext)
where dataNtext is not null
update Table
set dataNVarChar = cast(dataNtext as nvarchar(max))
where dataNtext is not null
And the error I get is:
Cannot create a row of size 8086 which is greater than the allowable maximum row size of 8060.
This is using SQL Server 2008.
Any help appreciated,
Thanks.
Update / Solution:
The marked answer below is correct, and SQL 2008 can change the column to the correct data type in my situation, and there are no dramas with the LINQ-utilising application we use on top of it:
alter table [TBL] alter column [COL] nvarchar(max)
I've also been advised to follow it up with:
update [TBL] set [COL] = [COL]
Which completes the conversion by moving the data from the lob structure to the table (if the length in less than 8k), which improves performance / keeps things proper.
