VARCHAR Vs NVARCHAR

Before Going for nvarchar and varchar we have to think about unicode ,non-unicode and ascii character set.

Unicode Character

Unicode's character set includes ALL human language's and written symbols.they are defined in unicode character table.

Non Unicode Character

a non unicode character is most probably a character or symbol mainly used in European countrys , simple example is English characters

ascii

There is a set of characters that are common in most generally used non-Unicode character sets and code pages. These characters are known as the ASCII characters. They include the common characters used in the English language and they occupy the first 128 code points (Hex 00-9F) in the non-Unicode code pages.

Point :

·         simply ascii is a subset of unicode

·        varchar only store non unicode characters while nvarchar can store Unicode character too

In the below example the chinese characters are unicode characters so they can  store only using nvarchar , varchar does not support those .

select '奥曼克' as testingone

Output :

testingone

------------

???

select N'奥曼克' as testingtwo

Output:

testingtwo

------------

奥曼克

declare  @test1 varchar(10) =N'奥曼克'

declare  @test2 nvarchar(10) =N'奥曼克'

declare  @test3 nvarchar(10) ='奥曼克'

select @test1

Output :

???

select @test2

Output:

奥曼克

select @test3

Output:

 ???

• N actually stands for National language character set
• N used for representing unicode characters.
• N tells to sql server  the data is unicode data and it is not a character data 

A little more information about varchar and nvarchar

select DATALENGTH(@test1) as data

Output 3 (varchar only occupy 1 byte for each character)

select DATALENGTH(@test2) as data

Output 6 (varchar only occupy 2 byte for each character)

so the maximum userdefined size for varchar will be 8000 and nvarchar will be 4000

declare @a nvarchar(4001)

will give error(maximum limit is 4000 characters)

declare @aa varchar(4001) this will  works fine