varchar and nvarchar

This is a bit off-topic. But today a had another case of an hour-long debugging session because of this. So here it goes:

If you need to work in SQL Server, and if you have a choice, always prefer nvarchar over varchar.

NEVER EVER USE VARCHAR IF YOU CAN AVOID ID!!!

USE NVARCHAR INSTEAD!!!

If ever you have experienced collation problems, you know what I’m talking about. And if not, just do it and thank me for the advice.

Why?

nvarchar stores strings as Unicode characters, whereas varchar stores in a 8-bit codepage. This means that nvarchar can store any special characters, whereas varchar can store only special characters of a specific codepage. As a result, your selects could look like data rubish, and you might have to convert between codepages and soon enough you find yourself doing things like

select columnName collate SQL_Latin1_General_CP1_CI_AS from tableName

In short: you don’t want to do that. So just use nvarchar. Period.

Still not satisfied? Yes, nvarchar uses more disk space than varchar. But no, this argument is not valid. The financial and emotional spend to fiddle around with code-page conversion problems is definitely bigger than the cumulated cost of hard disk space thus far produced by mankind.

You don’t need any special characters for your application? Maybe not now, but soon enough you will. Financial instrument names are a good example. They have copyright symbols, tilde, and other funny looking characters. And they are passed often between systems in interfaces. With varchar: the horror, the horror…

Leave a Reply