VARCHAR, NVARCHAR, and CHAR are data types used to store character strings, but they have different characteristics and use cases. Below are the differences between these data types, along with examples for better understanding.

1. VARCHAR

• Definition: VARCHAR stands for Variable Character. It is used to store non-Unicode character data.
• Storage: It uses 1 byte per character, plus 2 bytes for length information.
• Length: You can specify a maximum length (e.g., VARCHAR(50)), and it can store up to 8,000 characters.
• Use Case: Use VARCHAR when you are storing ASCII characters and you want to save space.

Example:

sql
1DECLARE @exampleVarchar VARCHAR(50);
2SET @exampleVarchar = 'Hello, World!';
3SELECT @exampleVarchar AS ExampleVarchar;

2. NVARCHAR

• Definition: NVARCHAR stands for National Variable Character. It is used to store Unicode character data.
• Storage: It uses 2 bytes per character, plus 2 bytes for length information.
• Length: You can specify a maximum length (e.g., NVARCHAR(50)), and it can store up to 4,000 characters (or up to 2 billion characters if you use NVARCHAR(MAX)).
• Use Case: Use NVARCHAR when you need to store characters from multiple languages or special characters (e.g., Chinese, Arabic).

Example:

sql
1DECLARE @exampleNvarchar NVARCHAR(50);
2SET @exampleNvarchar = N'??,??!'; -- Chinese for "Hello, World!"
3SELECT @exampleNvarchar AS ExampleNvarchar;

3. CHAR

• Definition: CHAR stands for Character. It is used to store fixed-length non-Unicode character data.
• Storage: It uses 1 byte per character.
• Length: You must specify a fixed length (e.g., CHAR(10)), and it will always use that amount of space, padding with spaces if the actual string is shorter.
• Use Case: Use CHAR when you know the exact length of the data you will store, such as fixed-length codes or identifiers.

Choosing the Right Data Type

• Use VARCHAR when you are dealing with standard ASCII characters and want to save space.
• Use NVARCHAR when you need to support multiple languages or special characters.
• Use CHAR when you have fixed-length data and want to ensure consistent storage size.

Let's try to understand ,determine the amount of memory each variable occupies.

SQL Code  Example :

sql
1DECLARE @Varchar VARCHAR(10) = 'Hello';
2DECLARE @Nvarchar NVARCHAR(10) = 'Hello';
3DECLARE @Char CHAR(10) = 'Hello';
4
5SELECT DATALENGTH(@Varchar) AS [Used Memory for Varchar];
6SELECT DATALENGTH(@Nvarchar) AS [Used Memory for NVarchar];
7SELECT DATALENGTH(@Char) AS [Used Memory for Char];

Explanation of Each Component