SQL-Varchar vs Nvarchar

VARCHAR (Variable-length, Non-Unicode)

• Storage - VARCHAR stores data using a single-byte character encoding (typically ASCII or a similar encoding). Each character takes up 1 byte of storage.
• Character Set - It can only store characters from the ASCII character set or other single-byte character sets (depending on the database's collation).
• Use Case - Ideal for storing English text or any text that only uses characters from the standard ASCII set or any single-byte character sets.

Example

Let's create a table to store names in VARCHAR. This is useful when you are only storing English or other ASCII-based characters.

CREATE TABLE Employees_Varchar (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
-- Inserting data with English names
INSERT INTO Employees_Varchar (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe'),
       (2, 'Alice', 'Smith');

NVARCHAR (Variable-length, Unicode)

• Storage - NVARCHAR stores data using Unicode, which uses a multi-byte character encoding. Each character typically requires 2 bytes (or more in some encodings, like UTF-8), allowing it to store a much wider range of characters.
• Character Set - NVARCHAR supports Unicode characters, which means it can store text from virtually any language (including languages with complex characters, like Chinese, Arabic, etc.).
• Use Case - Ideal for storing text that includes characters from multiple languages or any text that includes special or non-ASCII characters (e.g., accents, symbols, or different alphabets).

Example

Now, let's create a similar table but use NVARCHAR. This is beneficial when you need to store multilingual data.

CREATE TABLE Employees_Nvarchar (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

-- Inserting data with names in English and Arabic
INSERT INTO Employees_Nvarchar (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe'),
       (2, 'أحمد', 'العلي'); -- Arabic text in the names