Menu
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).
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!
985
SQL Server uses the data type precedence to determine which the target data type is. NVARCHAR has higher precedence than the VARCHAR data type. Therefore, during the data type conversion, SQL Server converts the existing VARCHAR values into NVARCHAR.
If you have a key on this table - insert the column data (and its key!) into a temporary table, drop the column, add the column back with the new datatype, then update it with the contents of the temporary table.
Today's development platforms or their operating systems support the Unicode character set. Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
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.
You may also be interested in enabling Unicode Compression for your table.
127
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
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
