1. Back To Blog

Explain the difference between 'NCHAR' vs 'NVARCHAR' & 'CHAR' vs 'VARCHAR' in SQL.

• By Shiv Prasad Koirala in SQL
• Sep 5th, 2017
• 13892
• 0

Contents

What is the difference between char vs nchar and char vs varchar ? SQL Server interview questions

• Introduction
• 'CHAR' vs 'VARCHAR' :
• 'NCHAR' vs 'NVARCHAR':

If you are new to SQL Server do see our training video series Learn SQL Server step by step.

What is the difference between char vs nchar and char vs varchar ? SQL Server interview questions

Introduction

In this article we will be going through the topic of SQL Server Data-types where we will be learning 'char', 'varchar', 'nchar' and 'nvarchar' & also try to find out the difference between these data-types.

'CHAR' vs 'VARCHAR' :

 

 

 

Above is a simple SQL Table been created which has two columns CountryCode and EnglishName with data types 'char' and 'varchar' respectively.

'char' data-type:

• It is a fixed length data-type.
• use to store non-Unicode characters(only English language).
• occupies 1 byte of space for each character.
• maximum character we can store in 'char' is 8000 bytes.

 

 

 

SQL Query:-

  

 

 

 

Output:-

 

 

 

As you can see above, the bytes occupied by the "IND" is 3 and even the length is 3 but the bytes occupied by "US" is 3 though the length is 2.

So char will occupy all bytes irrespective of your data length while varchar is variable and depends on actual data.

'varchar' data-type:

• It is a variable length data-type.
• use to store non-Unicode characters(only English language).
• occupies 1 byte of space for each character.
• maximum character we can store in 'varchar' is 8000 bytes.

Rows edited :-

 

 

 

SQL Query :-

 

 

 

 

Output :-

 

 

 

As you can see above, it is showing the same lenEnglishName(number of bytes, 5 for India and 12 for United States) which is equal to number of characters.
Thus the character stored in the column as data-type 'varchar' will not occupy all the bytes to store the value and it will avoid wasting the space in database.

So from the above information, we understood the 'char' and 'varchar' data-type and the difference between them.

Now let's try to understand other two data-types that are 'nchar' and 'nvarchar'

NCHAR' vs 'NVARCHAR': 

  

 

 

Above is a simple SQL Table been created which has two columns NickName and SurName with data types 'nchar' and 'nvarchar' respectively.

'nchar' data-type:

• It is a fixed length data-type.
• use to store Unicode(rather than English language) as well as non-Unicode characters.
• occupies 2 byte of space for each character.
• maximum character we can store in 'nchar' is 4000 bytes.

Rows edited :- 

 

 

 

SQL Query :-

 

Output :-

 

 

 

As you can see above, the lenNicName column shows 20 bytes even though the size is declared is 10.

It's because NCHAR holds 2 bytes of space for each character.

'nvarchar' data-type:

• It is a variable length data-type.
• use to store Unicode(rather than English language) as well as non-Unicode characters.
• occupies 2 byte of space for each character.
• Maximum character we can store in 'nvarchar' is 4000 bytes.

Rows edited :-

 

 

 

SQL Query :-

 

 

 

 

Output:

 

 

As we can see above, the lenSurName column is showing 18 for Fernandis and 14 for Rastogi as a value.
This is because it occupies 2 bytes of space for each character and the data length is only 9 and 7 characters respectively.
Therefore it occupies 18 and 14 bytes respectively of space in database.

So from the above information, we understood the 'nchar' and 'nvarchar''data-type and the difference between them.

Get revised with most asked SQL Server Interview Questions from below 1 hour tutorial :-