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