From: Joel on
I have a sqlserver 2008 database that is currently using varchars. I need
to move this to unicode.

What is the best way to accomplish this task? Should I recreate the
database using a certain character set (like utf-8) and then migrate the
data or do I just do an alter table and move the fields from varchars to
nvarchars?

I am not real familiar with best practices for SQL Server and unicode.

Thanks


From: Erland Sommarskog on
Joel (nowhere(a)nowhere.com) writes:
> I have a sqlserver 2008 database that is currently using varchars. I need
> to move this to unicode.
>
> What is the best way to accomplish this task? Should I recreate the
> database using a certain character set (like utf-8) and then migrate the
> data or do I just do an alter table and move the fields from varchars to
> nvarchars?

This is a fairly complex operation, no matter how you do it - the moral is
that you should do it right from the beginning.

At first glance, ALTER TABLE ALTER COLUMN may seem to be the way to go,
but this command can only handle one column at a time, and since the
change requires a physical change to the table, that can hurt. Even more
problematic is that all indexes referring to the columns needs to be dropped
first, and likewise PRIMARY KEY, UNIQUE and FOREIGN KEY constraints.
(Actually also CHECK and DEFAULT constraints.)

So it may be better to build a new database from scripts and copy data
over. But obviously that can take some time. And it is a difficult
operation in itself.

As for the character conversion, that is no problem. If you just run INSERT
statements, SQL Server will convert characters as needed.

Note also, that you cannot store data as UTF-8 in SQL Server. SQL Server
uses UCS-2 to store Unicode.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx