varchar vs nvarchar

What are the differences between varchar and nvarchar in Microsoft SQL Server? Question quite common on the recruitment tests, but the question: when and why we should use this one or this one is not so simple.

First, let’s take a look what are varchar and nvarchar:

• nvarchar- variable-length, it takes more space than varchar, can store any Unicode data, dependent on the database collation,
• varchar - restricted to an 8-bit codepage (but not in every collation), single-byte character data, it takes less space than nvarchar, it cannot contain Unicode characters, the database collation determines which code page the data is using.

The next question which we should ask ourselves is: when we should use varchar and in which situations we should use nvarchar ?

nvarchar: - When your application need to be translated to e.g. Arabic, Mandarin, Hebrew, Japanese or other languages with 16-bit encoding, or at least there is a big possibility that it will be translated in the future. - When your application will use very small database, you can use nvarchar (for convenience) for every text column. - When you do care more about convenience than disk space, memory and effectiveness.

varchar: - When you will NEVER use Unicode. - When performance and effectiveness is important for you. - When you want to save some space and memory.

Before choosing one of those two types remember about the following things:

• Changing the data type from varchar to nvarchar and vice versa is very expensive, especially if the project is in a later phase.
• When system has a big data base and the effectiveness is important, consider using varchar – it is faster.
• You should not do this, but … if you make JOIN on the columns with varchar and nvarchar, then there is a big performance hit.
• When you are using nvarchar in stored procedures or scripts use prefix N (e.g. DECLARE @name = N'The name';) which prevent string type conversions at runtime.

Summarizing: choice between varchar and nvarchar depends on what type of application you are writing and what is important for you (effectiveness or convenience).