In this article I am explain, difference between the VARCHAR andNVARCHAR Data Type in Sql Serve.
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) ='Jitendra' 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)= 'Jitendra' SELECT @FirstName |
No. of Bytes
required for each character
|
It takes 1 byte per
character
Example:
DECLARE @FirstName AS VARCHAR(50) = 'Jitendra' SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName LengthJitendra 8 |
It takes 2 bytes per
Unicode/Non-Unicode character.
Example: DECLARE @FirstName AS NVARCHAR(50)= 'Jitendra' SELECT @FirstName AS FirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length Jitendra 16 |
Optional Parameter nrange
|
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 nis 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 ='Jitendra' SELECT @firstName FirstName,DATALENGTH(@firstName) Length Result: FirstName Length J 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 ='Jitendra' SELECT @firstName FirstName,DATALENGTH(@firstName) Length Result: FirstName Length J 2 |
If Optional
Parameter nis 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 22.Example:
DECLARE @firstName VARCHAR(22)= 'Jitendra Gangwar INDIA ASIA' SELECT CAST(@firstName AS VARCHAR) FirstName, DATALENGTH(CAST(@firstName AS VARCHAR)) Length Result: FirstName Length Jitendra Gangwar INDIA 22 |
When this optional
parameter n is not specified while using the CAST
CONVERT functions, then it is considered as 22.Example:
DECLARE @firstName NVARCHAR(22)= 'Jitendra Gangwar INDIA ASIA' SELECT CAST(@firstName AS'NVARCHAR) FirstName, DATALENGTH(CAST(@firstName AS'NVARCHAR)) Length Result: FirstName Length Jitendra Gangwar INDIA 44 |
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
characters stored. The value of n defines maximum no. of
characters that can be
stored.