将现有 SQL Server 2005 数据库中的数据类型 varchar 更改为 nvarchar。有什么问题吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8157602/
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
Change datatype varchar to nvarchar in existing SQL Server 2005 database. Any issues?
提问by proggrock
I need to change column datatypes in a database table from varcharto nvarcharin order to support Chinese characters (currently, the varcharfields 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!
我知道如何更改这些值，但我想看看这样做是否安全。换衣服之前有什么需要注意的吗？谢谢！
回答by Lucas
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
在主键字段上它不起作用 - 您必须重新创建表
回答by Remus Rusanu
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 CLEANTABLEto 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 Compressionfor your table.
您可能还对为您的表启用Unicode 压缩感兴趣。
回答by Garrett Vlieger
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。
回答by Scott Bruns
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。
回答by Andy Robertson
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。还要检查表是否在复制中！这可能会给您带来一系列新问题！
