Difference between Varchar and Nvarchar data types in SQL Server

This is really an interesting topic for me as well as for many developers who all are working on SQL Server.I have seen many developers who are quite confused in differentiating between Varchar and Nvarchar datatypes.I hope after reading my article, you will be able to answer yourself where to use Varchar and where to use Nvarchar.

So let’s see what is the main difference between Varchar and Nvarchar datatype.

There are mainly two differences between Varchar and Nvarchar data types.

1. Varchar data type can only store non Unicode values while Nvarchar data type can store Unicode + Non Unicode values.
2. Varchar data type takes 1 byte per character while Nvarchar data type takes 2 bytes per character.

Unicode Characters :- It is the International standard of a character encoding and decoding that is globally accepted.

All the characters from different languages can be encoded by Unicode standard.

Non Unicode Standard or Traditional Standard:- In earlier days when Unicode characters standard was not there for representation of a character,

We were using ASCII standard that was basically based on American and European languages encoding of characters.

Let’s take an example to understand this fact clearly.

Declare @NameNonUnicode as varchar(100)

Declare @NameUnicode as Nvarchar(100)

Set @NameUnicode='Neeraj Kumar Yadav'

Set @NameNonUnicode ='Neeraj kumar Yadav'

Select @NameNonUnicode as NonUnicode, @NameUnicode as Unicode

I have assigned a Non Unicode value to both variables and if I run the above code, below will be the output.

Now let’s assign nontraditional characters(Other than English) to the declared variables and see what results comes up.

In the above picture , we can see the result as “????” for both variables and the reason behind this is that SQL Sever is not able to recognize the characters assigned to the variables.Hence SQL is unable to decode the characters correctly. Let’s take another scenario.

I put character “N” just before the assigned values and you can see the difference in the result set.

The Varchar variable is still showing “????” (unrecognized characters) while Nvarchar variable has encoded the characters correctly.

Relevance of using character “N”:- Character “N” tells SQL server that the values assigned to the variable will contain Unicode characters (Nontraditional characters).

When we didn't put “N” just before the assignment , there was no difference in the result set of Varchar and Nvarchar variables.

Conclusion :- Basically for multilingual application we use Nvarchar data types otherwise we won’t be able to decode the characters correctly.

Second difference between Nvarchar and Varchar data type is the length , below is the example for the same. Nvarchar takes 2 bytes per character while Varchar takes 1 byte per character.