Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when changing field types?

I have a table with about a million records that has several VARCHAR type fields. I would like to change these to NVARCHAR, but as I understand it, this will take some time and resources, as the fields are in the middle of the table, and SQL Server has to do a bunch of copying/re-ordering.

What is an efficient way of accomplishing this?

One way might be to:

• Add a NULLable NVARCHAR column

• Using batches, update a number of rows at a time (e.g. 1000 or 10000 rows)

• Backup the log, checkpoint, what have you in between batches

• When all the rows have been updated, drop the old column and rename the new one

• Rebuild indexes

This won't be faster in the long run, and still requires a maintenance window (since you don't want users updating rows you've already updated unless you put a temporary trigger in place to counter that), but it will prevent a huge transaction and after a few updates will give you more predictability about how long it will take.

You could do the same thing by creating a new table, and renaming that once it's done... while this avoids the need for step 5, it would cause even more data churn and can be more problematic due to constraints, foreign keys, triggers etc. that might be involved with the table.

StackExchange Database Administrators Q#21186, answer score: 20