Converting ntext to nvcharmax(max) - Getting around size limitation
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.
Answer
That is most likely because the column dataNVarChar is not defined as NVARCHAR(max) To convert a column from NTEXT to NVARCHAR(MAX), use this
alter table TBL alter column COL nvarchar(max)
It will perform the conversion of the data in the column for you at the same time