Before Going for nvarchar and varchar we have to think about unicode ,non-unicode and ascii character set.
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
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.
· 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
select N'奥曼克' as testingtwo
declare @test1 varchar(10) =N'奥曼克'
declare @test2 nvarchar(10) =N'奥曼克'
declare @test3 nvarchar(10) ='奥曼克'
- 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