Example: Converting a Unicode Database to UTF-8

A fictitious database named xpubs is modified to use univarchar columns.

Prerequisites

Assume a database was created using the following script on a server that has all the installation defaults, namely character set “iso_1” and default sort order ID 50, “binary_iso_1”:

create database xpubs
go
use xpubs
go
create table authors (au_id int, au_lname varchar(255), au_fname varchar(255))
go
create index au_idx on authors(au_lname, au_fname)
go

Then the data was loaded into the server using a series of inserts and updates.

To convert the data to UTF-8:

Task

Extract the data and convert it to UTF-8 form (the conversion occurs with the -J parameter):
```
% bcp xpubs..authors out authors.utf8.bcp -c -Jutf8 -Usa -P
```

Install UTF-8 as the default character set:

charset -Usa -P binary.srt utf8
isql -Usa -P
sp_configure 'default sortorder id', 50, 'utf8'

Shutdown the server.
Restart the server and modify the default character set and re-create indexes on the system tables. :
```
isql -Usa -P
sp_dboption xpubs, 'select into', true
go
use xpubs
go
checkpoint
go
delete from authors
go
quit
```
Restart the server.

Reload the data:

bcp xpubs..authors in authors.utf8.bcp -c -Jutf8 -Usa -P

Migrating Selected Columns to unichar
With a working database running with UTF-8 as the default character set, you can convert select columns to univarchar.
Migrating to or from unitext
The alter table modify command does not support text, image, or unitext columns. To migrate from a text to a unitext column, you must first use bcp, create a table with unitext columns, and then use bcp again to place data into the new table. This migration path only works when you invoke bcp with -Jutf8 option.

Parent topic: Changing the character set, sort order, or message language