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.
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:
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.
Character Data Type
Variable-length and non-Unicode characters
Variable-length, both Unicode and non-Unicode characters like as Chinese, Korean, or Japanese.
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.
Real Length (in bytes)
Two(2) times Actual Length (in bytes)
Takes up one(1) byte per character
Takes up two(2) bytes per Unicode/Non-Unicode character
Range From 1 to 8,000 characters
The Range from 1 to 4,000 characters
Advantages and Disadvantages of VARCHAR Vs NVARCHAR
Since it’s variable length it takes less memory spaces.
Decreases the performance of some SQL queries.
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.
CASE-IWe can declare the variable and assign variable name at the same time of declaration.
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.
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.
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
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.
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.
DECLARE@Address VARCHAR(30)='M G Road Shivpuri Delhi East'