I need to change column datatypes in a database table from varchar to nvarchar in order to support Chinese characters (currently, the varchar fields that have these characters are only showing question marks).
我需要将数据库表中的列数据类型从varchar更改为nvarchar以支持中文字符（目前，具有这些字符的varchar字段仅显示问号）。
I know how to change the values, but I want to see if it's safe to do so. Is there anything to look out for before I do the changing? Thanks!
我知道如何更改值，但我想知道这样做是否安全。在我改变之前有什么需要注意的吗？谢谢！
5 个解决方案
#1
26
Note that this change is a size-of-data update, see SQL Server table columns under the hood. The change will add a new NVARCHAR column, it will update each row copying the dta from the old VARCHAR to the new NVARCHAR column, and then it will mark the old VARCHAR column as dropped. IF the table is large, this will generate a large log, so be prepared for it. After the update, run DBCC CLEANTABLE to reclaim the space used by the former VARCHAR column. If you can afford it , better run ALTER TABLE ... REBUILD, which will not only reclaim the space it will also completely remove physical deleted VARCHAR column. The linked article at the beginning has more details.
请注意，此更改是一种数据大小更新，请参阅引擎盖下的SQL Server表列。此更改将添加新的NVARCHAR列，它将更新将dta从旧VARCHAR复制到新NVARCHAR列的每一行，然后它将旧的VARCHAR列标记为已删除。如果表很大，这将生成一个大的日志，所以要做好准备。更新后，运行DBCC CLEANTABLE以回收前VARCHAR列使用的空间。如果你负担得起，最好运行ALTER TABLE ... REBUILD，它不仅会回收空间，还会完全删除物理删除的VARCHAR列。一开始的链接文章有更多细节。
You may also be interested in enabling Unicode Compression for your table.
您可能还有兴趣为您的表启用Unicode压缩。
#2
18
You can do on non primary key fields:
您可以在非主键字段上执行以下操作：
ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] nvarchar(N) null
On the primary key fields it will not work - you will have to recreate the table
在主键字段上它将无法工作 - 您将不得不重新创建表
#3
7
Make sure that the length doesn't exceed 4000 since the maximum for VARCHAR is 8000 while NVARCHAR is only 4K.
确保长度不超过4000，因为VARCHAR的最大值为8000，而NVARCHAR仅为4K。
#4
2
The table will get bigger. Each character in the column will take twice the space to store. You might not notice unless the table is really big.
桌子会变大。列中的每个字符将占用两倍的空间来存储。除非桌子真的很大，否则你可能不会注意到。
Stored procedures/views/queries that work with the column data might need to be modified to deal with the nvarchar.
可能需要修改使用列数据的存储过程/视图/查询以处理nvarchar。
#5
1
Check all the dependencies for this table as stored procs, functions, temp tables based on this table and variables used for inserts/updates etc may also need to be updated to NVARCHAR. Also check if the table is in replication! That could cause you a new set of problems!
检查此表的所有依赖项，因为基于此表的存储过程，函数，临时表以及用于插入/更新等的变量也可能需要更新为NVARCHAR。还要检查表是否正在复制！这可能会给你带来一系列新问题！
#1
26
Note that this change is a size-of-data update, see SQL Server table columns under the hood. The change will add a new NVARCHAR column, it will update each row copying the dta from the old VARCHAR to the new NVARCHAR column, and then it will mark the old VARCHAR column as dropped. IF the table is large, this will generate a large log, so be prepared for it. After the update, run DBCC CLEANTABLE to reclaim the space used by the former VARCHAR column. If you can afford it , better run ALTER TABLE ... REBUILD, which will not only reclaim the space it will also completely remove physical deleted VARCHAR column. The linked article at the beginning has more details.
请注意，此更改是一种数据大小更新，请参阅引擎盖下的SQL Server表列。此更改将添加新的NVARCHAR列，它将更新将dta从旧VARCHAR复制到新NVARCHAR列的每一行，然后它将旧的VARCHAR列标记为已删除。如果表很大，这将生成一个大的日志，所以要做好准备。更新后，运行DBCC CLEANTABLE以回收前VARCHAR列使用的空间。如果你负担得起，最好运行ALTER TABLE ... REBUILD，它不仅会回收空间，还会完全删除物理删除的VARCHAR列。一开始的链接文章有更多细节。
You may also be interested in enabling Unicode Compression for your table.
您可能还有兴趣为您的表启用Unicode压缩。
#2
18
You can do on non primary key fields:
您可以在非主键字段上执行以下操作：
ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] nvarchar(N) null
On the primary key fields it will not work - you will have to recreate the table
在主键字段上它将无法工作 - 您将不得不重新创建表
#3
7
Make sure that the length doesn't exceed 4000 since the maximum for VARCHAR is 8000 while NVARCHAR is only 4K.
确保长度不超过4000，因为VARCHAR的最大值为8000，而NVARCHAR仅为4K。
#4
2
The table will get bigger. Each character in the column will take twice the space to store. You might not notice unless the table is really big.
桌子会变大。列中的每个字符将占用两倍的空间来存储。除非桌子真的很大，否则你可能不会注意到。
Stored procedures/views/queries that work with the column data might need to be modified to deal with the nvarchar.
可能需要修改使用列数据的存储过程/视图/查询以处理nvarchar。
#5
1
Check all the dependencies for this table as stored procs, functions, temp tables based on this table and variables used for inserts/updates etc may also need to be updated to NVARCHAR. Also check if the table is in replication! That could cause you a new set of problems!
检查此表的所有依赖项，因为基于此表的存储过程，函数，临时表以及用于插入/更新等的变量也可能需要更新为NVARCHAR。还要检查表是否正在复制！这可能会给你带来一系列新问题！