Difference Between CHAR, VARCHAR, NCHAR, and NVARCHAR in SQL

Introduction

When working with a database, you often need to store text, such as names, addresses, or descriptions. SQL offers different types of text storage: CHAR, VARCHAR, NCHAR, and NVARCHAR. Although they all store text, they differ in how they store and handle data. Understanding these differences can help you make the right choice and optimize your database.

What is CHAR?

• Fixed Length: CHAR stores text in a fixed length format. Even if the text you store is shorter than the defined length, CHAR will fill up the remaining space with blanks.
• Example: If you define a CHAR(5) field and store "ABC", the database will store it as "ABC " (with 2 extra spaces to make it 5 characters long).
• When to Use: Use CHAR when all the data in a column has the same length. For example, country codes (like "USA" or "IND") are always 3 characters, making CHAR a good fit.
  

What is VARCHAR?

• Variable Length: VARCHAR stores text in a variable length format. It only takes up as much space as the text you actually store, without adding extra spaces.
• Example: If you define a VARCHAR(10) field and store "ABC", it only uses 3 characters of space, not 10.
• When to Use: Use VARCHAR when the text length varies. For example, names or descriptions, which may be short for some records and long for others.

What is NCHAR?

• Fixed Length with Unicode Support: NCHAR is like CHAR, but it stores Unicode data, which allows for special characters from multiple languages.
• Example: If you define an NCHAR(5) field and store "हेलो" (Hindi for "hello"), it will store the two characters and fill up the remaining 3 characters with blanks.
• When to Use: Use NCHAR when you need to store fixed-length data that includes special characters, like text in different languages.

What is NVARCHAR?

• Variable Length with Unicode Support: NVARCHAR is like VARCHAR, but it stores Unicode text. It only uses as much space as the actual text, without adding extra spaces.
• Example: If you define an NVARCHAR(10) field and store "हेलो", it will only use 2 characters of space for the text, without padding.
• When to Use: Use NVARCHAR when you need to store variable-length text that includes special characters, like names or descriptions in different languages.

Key Differences

Type	Fixed/Variable	Unicode Support	Use Case
CHAR	Fixed	No	Fixed-length, simple text (e.g., country codes)
VARCHAR	Variable	No	Varying-length text (e.g., names, descriptions)
NCHAR	Fixed	Yes	Fixed-length, special character text (e.g., foreign characters)
NVARCHAR	Variable	Yes	Varying-length, special character text (e.g., multilingual data)

Conclusion

The choice between CHAR, VARCHAR, NCHAR, and NVARCHAR depends on your data. If the text always has the same length, choose CHAR or NCHAR. If the length varies, go for VARCHAR or NVARCHAR. If you're dealing with multiple languages or special characters, opt for NCHAR or NVARCHAR for Unicode support.