SQL Server VARCHAR VS NVARCHAR

What is the difference Between VARCHAR and NVARCHAR data types?

VARCHAR

• VARCHAR data type is used to store non-Unicode string data of variable length.
• Stores data at 1 byte per character.
• Supports up to 8000 characters.

Syntax
varchar(n)
or
varchar(max)

n specify the string length that ranges from 1 to 8000. Default value for n is 1.

max specify the maximum storage size which is 2³¹-1 bytes (2 GB)

NVARCHAR

• NVARCHAR data type is used to store Unicode string data of variable length, Can store both Unicode and non-Unicode strings.
• It stores data at 2 byte per character.
• It supports up to 4000 characters.

Syntax
nvarchar(n)
or
nvarchar(max)

n specify the string length that ranges from 1 to 8000. Default value for n is 1.

max specify the maximum storage size which is 2³¹-1 bytes (2 GB)

Difference between VARCHAR vs NVARCHAR

Data Storage Vs Length

VARCHAR

In following T-SQL, declared a variable @str that is of datatype varchar of size 30.

DECLARE @Str VARCHAR(30)
SET @Str = 'Microsoft' 
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength, 
LEN(@Str) AS StrText_length

As varchar has a variable-length and takes 1 bytes per character to store the data so it takes 9 bytes to store @strt value ‘microsoft’ that is length of @strt * 1 bytes (9 *1) =9.

NVARCHAR

In following T-SQL, declared a variable @str that is of datatype nvarchar of size 30.

DECLARE @Str NVARCHAR(30)
SET @Str = 'Microsoft' 
SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength, 
LEN(@Str) AS StrText_length

As nvarchar has a variable-length and takes 2 bytes per character to store the data so it takes 9 bytes to store value ‘microsoft’ that is length of @str* 2 bytes (9 *2) =18.

 

Using VARCHAR and NVARCHAR to store a Unicode data

VARCHAR

Varchar can not store Unicode data , here we try to store Hindi language text to @str variable of type varchar.

DECLARE @Str VARCHAR(30)

SET @Str = N'माइक्रोसॉफ्ट'

SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength,
LEN(@Str) AS StrText_length

As you can see, it does not support Unicode characters or multilingual data.

NVARCHAR

NVARCHAR can store Unicode data , again we try to store hindi language text to @str variable of type nvarchar.

Note: Always make sure that you prefix Unicode string literals with an N prefix.

DECLARE @Str NVARCHAR(30)

SET @Str = N'माइक्रोसॉफ्ट'

SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength,
LEN(@Str) AS StrText_length

As you can see, it supports the Unicode data that you can see in the first column of result set.

Also Read..

SQL Server CHAR VS NCHAR