unicode conversion
in [Server Configuration]
|
Prev: Best Practice for migrating from ntext to nvarchar(max) in SQL 200
Next: receive emails into SQL server
From: Joel on 19 Apr 2010 11:59 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 19 Apr 2010 17:48
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 |