Home » SQL Server VARCHAR VS NVARCHAR

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 231-1 bytes (2 GB)

NVARCHAR

  • NVARCHAR data type is used to store Unicode string data of variable length, Can store both unicode and non-unicoden 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 231-1 bytes (2 GB)

Lets Look at an example to demonistarte the difference between VARCHAR vs NVARCHAR

Data Storage Vs Length

  • VARCHAR

Here we have a variable @strtxt 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 @strtxt value ‘microsoft’ that is lenght of @strtxt * 1 bytes (9 *1) =9.

 

  • NVARCHAR

Here we have a nvariable @strtxt of datatype varchar 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 @strtxt value ‘microsoft’ that is length of @strtxt * 2 bytes (9 *2) =18.


 

Can store 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

Recommended for you

SQL Server CHAR VS NCHAR




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.