Change datatype varchar to nvarchar in existing SQL Server 2005 database. Any issues?

Question

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!

Cioban

Answer 1

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 data 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.

Bridgeboard

Answer 2

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

Fogle

Or drop then recreate the primary key, and any foreign keys that refer to it – Refrigeration

Answer 3

Make sure that the length doesn't exceed 4000 since the maximum for VARCHAR is 8000 while NVARCHAR is only 4K.

Vezza

By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored. NVARCHAR(MAX) may hold up 2GB of data – Sweeping

Answer 4

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.

Substitute

Answer 5

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!

Ayakoayala

Answer 6

Using Remus' answer for reference, here's a complete script that does the ALTER COLUMN and then reclaims the space. Note that on a mid-spec laptop with 16 GBs of RAM, this took less than 5 minutes to run on a table with > 5 million records. Less than 1 minute on my desktop workstation and the production server.

ALTER TABLE TableName ALTER COLUMN ColumnName nvarchar(250) not null

DBCC CLEANTABLE (DatabaseName, 'TableName', 100000)

ALTER TABLE TableName REBUILD

Dumbwaiter