Change data type from varchar to nvarchar for a table which has a large amount of data

Question

I need suggestions on how I would best go about changing a column's data type from varchar to nvarchar for a table which has 500 million records.

Thanks in advance.

Answer 1

So, all what i did was, 1. Create a new table with nvarchar for required column 2. copy data from existing table to new table using ssis. 3. Create constraints and indexes on new table. All these steps can be done when the app is still up and running 4. Rename existing table to _Old and rename new table to existing table's name Above step hardly takes a second and is the only step when the app is down.

Like this i was able to reduce the app down time and successfully changed column data type.

Answer 2

for a table which has 500 million records.

While Larnu answer is very much correct, due to size of the table I would like to point to this thread:

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

and url: http://rusanu.com/2011/10/20/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.

Unfortunately, VARCHAR()->NVARCHAR() is not metadata only change, so not like INT->BIGINT

Therefore, a suggestion to run a change at a maintenance window with further removal of a column that is marked as "deleted":

DBCC CLEANTABLE 
-- or
ALTER TABLE ... REBUILD

Answer 3

You just need to issue an ALTER TABLE statement

ALTER TABLE YourTable ALTER COLUMN YourColumn nvarchar({required length});

You'll need to replace the section in braces ({}) and replace with your object names.