Convert Varchar Column to Nvarchar – Techniques and Best Practices

varchar to nvarchar

As the world shrinks in size it is becoming more and more common, to encounter Unicode data, and require columns converted from the Varchar datatype to the nvarchar datatype.

What is Unicode data? The easiest way to describe it is “special characters” e.g Chinese or Japanese writing.

It’s important to keep in mind when designing database applications, that Unicode data might be used and plan for this.
.

Recently I was tasked with changing the field on a table from varchar to nvarchar. I asked myself four important questions.

1./ Would I be able to convert the column, without dropping and recreating the table

Yes, this is completely possible however, it is not the most ‘efficient’ method of doing so.

A study done by Microsoft a few years ago demonstrated that the most efficient method of implementing such a change were by either creating another table and populating or even better doing so via a flat file.

2./ Do I need to increase the data size at the same time

Changing a column to Nvarchar will mean that behind the scenes the column will use twice as much data to store the information. This is because it now has room for extra character information.

3./ What would happen if I needed to rollback?

If you rollback before any extra character data is added to your table, you will not lose anything by deconverting. If however you do have extra characters the information will turn to gibberish.

4./ What kind of performance impact would this change involve?

While the column is being altered, SQL will lock the table, so expect an outage period.

There are a number of different methods you can use to carry out this column conversion, aside from the simple T-SQL solution. If you are searching for the most efficient method this Microsoft article compares conversions done in a number of different scenarios.

Also after implementation nvarchar can be less optimal than varchar when it comes to the general performance of the database, with this in mind it is important to do performance testing to gauge the impact of this.

T-SQL Example of Varchar to Nvarchar Conversion SQL Server

Changing from varchar to nvarchar is a pretty straightforward process. It is possible to do so with a simple alter table SQL command, or via the SSMS tool.

if you have a varchar (10) you can simply change it to a nvarchar(10), you don’t need to make it a nvarchar(20) however it will use twice as much space/data behind the scenes.

When you rollback this change, you can risk losing data if any special character type data has been added, since you changed the field to non Unicode.

T-SQL Example – Setting up the data

Note: Ensure you specify a Unicode string with a capital N when you insert (as in the example below)
.
----------------------------------------
USE DBAFire_DB

CREATE TABLE [varchar-nvarchar-conversion]

(id int,
textcopy varchar(24))

INSERT INTO [varchar-nvarchar-conversion]
SELECT 1,'normal text'
UNION ALL
SELECT 2,'more normal text'



-- now try to insert some chinese
INSERT INTO [varchar-nvarchar-conversion]
SELECT 3,N'普通文本'

--view the results
select * from [varchar-nvarchar-conversion]
----------------------------------------

varchar to nvarchar

T-SQL Example – Altering the column

----------------------------------------
--convert the column

ALTER TABLE [varchar-nvarchar-conversion] ALTER 
COLUMN textcopy NVARCHAR(24)

-- remove previous chinese attempt

DELETE FROM [varchar-nvarchar-conversion] WHERE id = 3

-- again, try to insert some chinese
INSERT INTO [varchar-nvarchar-conversion]
SELECT 3,N'普通文本'

--view the results
select * from [varchar-nvarchar-conversion]
----------------------------------------

If you convert the column back to a VARCHAR again the question marks will reappear and any Unicode data will be lost.

Things to Keep In Mind Post Convert Varchar Column to Nvarchar

  • Make sure you check for implicit conversions
  • Ensure you have enough space
  • Rollback sooner rather than later
  • Check code, reports, SSIS packages that access the field. End to end testing of everything that touches it

Anything else you have found out whilst converting Varchar datatypes?

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

1 Comment

Leave a Reply