Nvarchar Vs Varchar Data Types In SQL Server

Nvarchar Vs Varchar? What’s the difference between varchar and nvarchar? These are one among the frequent questions from people who are new to SQL Server programming. These string data types have many similarities and some differences. Here I’ve compiled the similarities, differences, advantages, disadvantages and the usage of varchar and nvarchar data types. These details will help you to understand and use them appropriately in your programs and applications. See the difference and similarities between char and nchar here.

Similarities Between Nvarchar And Varchar

1. Both varchar and nvarchar are variable length string data.
2. Their maximum storage capacity is 8000 bytes.
3. Both has an optional argument specified as “n”. i.e. varchar [(n|max)] and nvarchar [(n|max)]. “n” defines the length of the string and “max” represents the maximum storage size.
4. While declaring a variable without specifying the argument “n”, then the default value is considered as 1.
5. If, n is not specified while using along with CAST function, the default length is considered as 30.
6. When declared with max, you can store up to 2 GB of string, i.e. 2^31-1 bytes.
7. Unlike char and nchar, the space used by varchar and nvarchar depends on the size of string stores in them.
8. Both the string data types can be used to store non-Unicode strings.
9. Index can be created on both the data types without using max. If you try to index varchar(max) and nvarchar(max), you will get an error (Column ‘<column-name>’ in table ‘<table-name>’ is of a type that is invalid for use as a key column in an index).

Example: Usage Of Varchar & Nvarchar

/* Using Varchar */
Declare @v as Varchar(50);
Set @v = 'Have a good day!';
Select @v;

/*Results:*/
--------------------------------------------------
Have a good day!

(1 row(s) affected)

/* Using Nvarchar */
Declare @n as Nvarchar(50);
Set @n = 'Have a good day!';
Select @n;

/*Results:*/
--------------------------------------------------
Have a good day!

(1 row(s) affected)

Varchar Vs Nvarchar: Differences

Example: Difference in storage size

Declare @v as Varchar(50);
Declare @n as Nvarchar(50);
Set @v = 'Have a good day!';
Set @n = N'Have a good day!';
Select DATALENGTH(@v), DATALENGTH(@n);

-- Results:
----------- -----------
16          32

(1 row(s) affected)

Nvarchar Vs Varchar: Advantages And Disadvantages

Reference

• About varchar in Microsoft docs.
• About nvarchar in Microsoft docs.