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

sql sql-server sql-server-2005 sqldatatypes
proggrock picture proggrock · Nov 16, 2011 · Viewed 52.6k times · Source

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!

Answer

Remus Rusanu picture Remus Rusanu · Nov 16, 2011

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.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More