Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:
Varchar[(n)] | NVarchar[(n)] | |
Basic Definition | Non-Unicode Variable Length character data type. Example: DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’ SELECT @FirstName |
UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters. Example: DECLARE @FirstName AS NVARCHAR(50)= ‘BASAVARAJ’ SELECT @FirstName |
No. of Bytes required for each character | It takes 1 byte per character
Example: |
It takes 2 bytes per Unicode/Non-Unicode character. Example: DECLARE @FirstName AS NVARCHAR(50)= ‘BASAVARAJ’ SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length BASAVARAJ 18 |
Optional Parameter n range | Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters. | Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters |
If Optional Parameter n is not specified in the variable declaration or column definition | If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1. Example: DECLARE @firstName VARCHAR = ‘BASAVARAJ’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length B 1 |
If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1. Example: DECLARE @firstName NVARCHAR = ‘BASAVARAJ’ SELECT @firstName FirstName, DATALENGTH(@firstName) Length Result: FirstName Length B 2 |
If Optional Parameter n is not specified in while using CAST/ CONVERT functions |
When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30.Example: DECLARE @firstName VARCHAR(35) = ‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’ SELECT CAST(@firstName AS VARCHAR) FirstName, DATALENGTH(CAST(@firstName AS VARCHAR)) Length Result: FirstName Length BASAVARAJ PRABHU BIRADAR INDIA 30 |
When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30.Example: DECLARE @firstName NVARCHAR(35) = ‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’ SELECT CAST(@firstName AS NVARCHAR) FirstName, DATALENGTH(CAST(@firstName AS NVARCHAR)) Length Result: FirstName Length BASAVARAJ PRABHU BIRADAR INDIA 60 |
Which one to use? | If we know that data to be stored in the column or variable doesn’t have any Unicode characters. | If we know that the data to be stored in the column or variable can have Unicode characters. |
Storage Size | Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset. | Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset. |
As both of these are variable length datatypes, so irrespective of the length (i.e. optional parameter n value) defined in the variable declaration/column definition it will always take the no. of bytes required for the actual charcters stored. The value of n defines maximum no. of characters that can be stored.
[ALSO READ] You may like to read below other popular articles on differences
Please correct me, if my understanding is wrong. Comments are always welcome
nice Article
Thank you
చాలా సవివరముగా తెలిపినందుకు…ధన్యవాదాలు.
Thank you BalakrishnamRaju
good explanation
Nice and simple explanation.
wow this is a best article 🙂
really helpful
Thanks
thank you
Concise, to the point, relevant. Wish more technical notes were written as skillfully as this one. Thanks for sharing.
Really Nice one and very simple to understand Thanks,Dinesh
Really helpful and understandable.
Nice Examples . Rajesh
Gud 1….
nice example
Great difference
Nice Difference to understand
Well explained…. 🙂
well done
good explanation dude. carry on.
Thanks,
Nice one.
helpful
thanks for this article.Very easy to understand
Thank you
good
Thank you… your way of explanation is good and easily understandable..
The explanation is good. How about doing some more such examples
Thank U dear!!!
thank u its very useful information for me , Because I am working on unicode charecter, Before that I was very confuse.
Good explanation
Nice
Its really very very important.
Nice artical
very nice../ 🙂
Good explaination.
i Like it.
thx
Good explaination
nice explanation.
Thanks a lot for the information.. It was helpful!
I really like your writing style, great info, thank you for putting up
gud very gud…
Brillant
Hello this is very nice post and very use full for us
This is very simple and understandable explanation. And it is very useful. Thanks Sir
Good
Thanks…! Very nice post 🙂
Brilliant !!
Love to see more articles.
Thank you Vishal
Good tech doc
Nice doc to refer brother……
Very thorough explaination
unicode data mean ??
It’s a very good article! It helped me a lot. Thank you so much!
Thank you Aninha
Enough explained..Thanks
Dear Basavaraj,
In this article mentioned that storage size for varchar is takes 1 byte per character + 2 bytes & nvarchar is takes 2 bytes per character + 2 bytes.
Here My question is, if the data tables created with nvarchar, then the total size of data base (ex DB size 2MB) is more than data tables created with varchar ( ex DB size 1MB). Pl, clarify.
Its very easy to understand..Thanks
Thank you Padmini
NIce
This is really fascinating, You are a very professional blogger.
I’ve joined your rss feed and sit up for in the hunt
for extra of your wonderful post. Also, I’ve shared your web
site in my social networks
thanks for me help
thanks for helpful this article
Nice!! Please do let me know about your other blogs.
can we create indexes on nvarchar data type column ?
Could you please provide me PDF for SQL Server tutorials?