C#: Should a standard .Net string be stored in varchar or nvarchar?

Question

We have an application written in C#, using .NET Framework 3.0 or 3.5 or something like that. As storage we use SQL Server, and we use Linq 2 SQL to talk with it.

Currently most (if not all) text columns in the database, are set to the varchar type (of different lengths of course).

But I started thinking... according to MSDN "The string type represents a sequence of zero or more Unicode characters." Does that mean that we should really change those columns to nvarchar for things to be stored correctly? Or how does that work? Their reasoning for setting them to varchar originally was because nvarchar requires twice as much space (if I have understood correctly). And as far as I have seen so far, it works with varchar, but we haven't done very much testing with unusal foreign characters...

Could someone shed some light on this?

Answer 1

Unless you've got text which is guaranteed to be representable in the code page of your database (or you're specifying the collation explicitly) I'd use nvarchar.

There are some cases where you can guarantee that the contents will be ASCII, in which case you could indeed use varchar - but I don't know how significant the benefits of that will be compared with the hassles of making absolutely sure that not only will the contents be ASCII, but you're never going to want anything other than ASCII (or within the specific code page).

Answer 2

There's a lot of perspective from the .net side of things. Here's a thought from the database side of things:

A varchar is half the size of an nvarchar. While this is not significant for many purposes, it is highly significant for indexes. An index that is half as wide, is twice as fast. This is because twice as many values can be stored on a datapage (the unit of database IO).

There are certain strings that you (from the app) control the construction of and would like to use to access important records. Alphanumeric identifiers (such as a customer number) fall into this category. Since you control the construction, you can force these to be safely varchar (and frequently do anyway). Why not gain the benefit of a half-sized double-fast index for this effort you're already doing?