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
  1. 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
  2. Install UTF-8 as the default character set:
    charset -Usa -P binary.srt utf8
    isql -Usa -P
    sp_configure 'default sortorder id', 50, 'utf8'
    
  3. Shutdown the server.
  4. 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
  5. Restart the server.
  6. Reload the data:
    bcp xpubs..authors in authors.utf8.bcp -c -Jutf8 -Usa -P