Nvarchar Vs Varchar Data Types In SQL Server

Nvarchar vs Varchar 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

#

Varchar

Nvarchar

1 Can store only non-Unicode string data. Similar to ASCII. Can store Unicode string data. It stores data in the form of UNICODE UCS-2 characters.
2 It just needs 1 byte to store a character. It needs 2 bytes to store a character.
3 The storage size is equal to the actual length of the data + 2 bytes. If the string data has 20 characters, the storage size will be 20 + 2 = 22 bytes The storage size is equal to double the actual length of the data + 2 bytes. If the string data has 20 characters, the storage size will be 40 + 2 = 42 bytes
4 Can store up to 8000 characters without using max parameter. Can store only up to 4000 characters without using max parameter.
5 Uses data pages to store the string data. Uses data pages as well as Text page when the data row exceeds 8000 bytes.

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

Varchar

Nvarchar

Advantages Occupies less physical storage space as it’s storage length is equal to the actual length of the data + 2 bytes. Supports multiple languages and locales. So any Unicode data can be stored without worrying about conversion.
Disadvantages Need to use conversion technique if you want to store Unicode date in varchar column. Occupies more physical storage space as it’s storage length is equal to double the actual length of the data + 2 bytes.
Usage Useful when the data you are planning to store is just non-Unicode ASCII character set Useful if you are planing to store data of different language or locale. It’s good to use Nvarchar, in case if you have any feature plan to include Unicode string to be stored in a column.

Reference


Leave your thoughts...

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