What is the Difference Between VARCHAR Vs NVARCHAR?

VARCHAR Vs NVARCHAR Summary: In this article, you will learn the basic differences between the VARCHAR and NVARCHAR data types in SQL Server and How to used to store characters, numbers or special characters.

Overview:

To store data as characters, special character and numeric values in a database, there are four(4) data types that can be used in SQL Server, These data types are:

What does N mean in SQL Server

First of all, let’s understand that what does N mean in SQL Server. You might surprise what the N stands for? N stands for National Language Character Set and Basically, it is used to specify a Unicode string. Therefore, when using the unicode data types, as per defined by the Unicode Standard a column can store any character, such as includes all of the characters defined in the various character sets.

.
Note:Note: Unicode data types take twice as much storage space as per comparision non-Unicode data types.

Unicode is often utilized in database applications that are designed to facilitate code pages that extend beyond the English and Western Europe code pages. It’s designed in order that extended character sets will still “fit” into database columns. What this implies is that Unicode character data types are limited to half the space, because each byte actually takes 2 bytes to store the data (Unicode is usually brought up as “double-wide”).

Furthermore, The SQL Server has supported the Unicode since SQL Server 7.0 by providing nchar/nvarchar/ntext data types. SQL Server does not support UTF-8 encoding for Unicode data, Therefore, but it will support UTF-16 encoding.

VARCHAR Vs NVARCHAR in SQL Server

I created a table below which will serve as a quick reference.

Property VARCHAR NVARCHAR
Character Data Type Variable-length and non-Unicode characters Variable-length, both Unicode and non-Unicode characters like as Chinese, Korean, or Japanese.
Usage It is used only when data length is variable or variable length columns, furthermore if the actual data is always way less than capacity Due to storage only, used only if you would like Unicode support such as the Japanese Kanji or Korean Hangul characters.
Storage Size Real Length (in bytes) Two(2) times Actual Length (in bytes)
Character Size Takes up one(1) byte per character Takes up two(2) bytes per Unicode/Non-Unicode character
Maximum Length Range From 1 to 8,000 characters The Range from 1 to 4,000 characters

Advantages and Disadvantages of VARCHAR Vs NVARCHAR

Data Types Advantages Disadvantages
VARCHAR Since it’s variable length it takes less memory spaces. Decreases the performance of some SQL queries.
NVARCHAR Supports several client computers that are running completely different locales If not properly used it may use up a lot of additional storage space.

Varchar Vs NVarchar

  • Varchar and NVarchar both are the data types which is used for declare data type of variables.
  • Both Varchar and NVarchar are Variable length character data type.
  • Varchar stores Non-Unicode character and NVarchar stores Unicode character.
  • We can write only ASCII values in Varchar. furthermore, ASCII values can only represent 256 characters. such as Capital A-Z, Small a-z, numbers from 0–9.

where as in NVarchar we can write ASCII values and special characters . Unicode values can represent more than 256 ASCII characters. Like Capital A-Z, Small a-z, number from 0–9 and special characters like Chinese or Japanese characters.

Example

CASE-IWe can declare the variable and assign variable name at the same time of declaration.


varchar vs nvarchar
 
CASE-II If we not assign variable at the time of declaration then we assign using set and then select the value by writing query such as select keyword then your variable name as new generated column name.


Example varchar vs nvarchar Output
 
CASE-III Furthermore, Also from the database we can directly create a table declare the variables.

 
CASE-IV Using edit option we can insert data in the database but when we insert more characters than data type length then it gives truncation error.
truncation error

  • Varchar is used when you want store only English characters or Non- Unicode characters, furthermore where as NVarchar is used where you want to store Unicode characters or non English characters.
  • A Varchar can stores maximum 8000 characters only where as NVarchar can store maximum 4000 Unicode/Non-Unicode characters only.
  • Varchar takes one byte of space for every character where as NVarchar takes two byte of space for every character

varchar vs nvarchar result
 

CASE-V

suppose, If the optional parameter value n is not menstioned in the variable declaration or column definition then by default, it is considered as 1.
i.e. If we just only declare @Variable_Name data_type where we not giving the length of data type then it consider as Varchar one (1) so it display only first letter of name.


VARCHAR parameter value n
 
CASE-VI The SQL DATALENGTH() function returns the number of bytes, it need to used to represent an expression.
The DATALENGTH() function in SQL Server counts the both leading( space between two words) and trailing(Space after last character) spaces when calculating the length of the expression.


varchar vs nvarchar DATACOUNT
 
CASE-VII Furthermore, when optional parameter n is not specified while using the CAST/ SQL CONVERT functions.


CAST and DATALENGTH Function
 
CASE-VIII SQL Server Len() function only display the Length of the string it not specifed that how many bytes used by Varchar or Nvarchar.


varchar vs nvarchar LEN Function