将 ntext 转换为 nvcharmax(max) - 绕过大小限制
我正在尝试将现有 SQL NText 列更改为 nvcharmax(max)，但遇到大小限制错误。我相信现有的数据量很大，其中一些数据超过了 8k 的限制。
我们希望对其进行转换，以便可以在 LINQ 中搜索该字段。
我尝试过的 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
我得到的错误是：
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 2008 可以将列更改为正确的数据类型，并且我们在其上使用的 LINQ 利用应用程序没有出现任何问题：
alter table [TBL] alter column [COL] nvarchar(max)
我也一直在建议跟进：
update [TBL] set [COL] = [COL]
通过将数据从 lob 结构移动到表（如果长度小于 8k）来完成转换，从而提高性能/保持正常。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这很可能是因为列 dataNVarChar 未定义为 NVARCHAR(max)
要将列从 NTEXT 转换为 NVARCHAR(MAX)，请使用它
它会同时为您执行列中数据的转换
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
It will perform the conversion of the data in the column for you at the same time