SQL 将 ntext 转换为 nvcharmax(max) - 绕过大小限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4708463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting ntext to nvcharmax(max) - Getting around size limitation
提问by Overflew
I'm trying to change an existing SQL NTextcolumn 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.
我正在尝试将现有的 SQL NText列更改为nvcharmax(max)，但遇到大小限制错误。我相信有大量现有数据，其中一些超过了 8k 的限制。
We're looking to convert this, so that the field is searchable in LINQ.
我们希望对其进行转换，以便可以在 LINQ 中搜索该字段。
The 2x SQL statements I've tried are:
我尝试过的 2x SQL 语句是：
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.
这是使用 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:
下面标记的答案是正确的，在我的情况下，SQL 2008 可以将列更改为正确的数据类型，并且我们在其上使用的使用 LINQ 的应用程序没有任何戏剧性：
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.
通过将数据从 lob 结构移动到表（如果长度小于 8k）来完成转换，从而提高性能/保持正常。
回答by RichardTheKiwi
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
这很可能是因为列 dataNVarChar 未定义为 NVARCHAR(max) 要将列从 NTEXT 转换为 NVARCHAR(MAX)，请使用此
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
它将同时为您执行列中数据的转换
