SQL Server - varchar and nvarchar

In SQL Server, the fundamental difference between varchar and nvarchar is how they store data and which characters they can handle.

The "n" in nvarchar stands for National, which indicates it uses the Unicode standard to represent characters from almost any language in the world.

Core Differences at a Glance

Feature	varchar	nvarchar
Storage	1 byte per character	2 bytes per character
Character Set	Non-Unicode (ASCII, local code pages)	Unicode (supports all languages)
Max Length	8,000 characters	4,000 characters
Literals	'Hello'	N'Hello' (Requires the N prefix)
Best For	English, numbers, symbols	International data, Emojis, Cyrillic, Asian scripts

---

1. Storage and Space

Because nvarchar uses Unicode (specifically UTF-16), it requires twice the storage space of varchar for standard English text.

• varchar(10): Uses 10 bytes to store "SQL Server".

• nvarchar(10): Uses 20 bytes to store "SQL Server".

This extra space isn't just about disk—it also means your indexes will be larger and your memory (buffer pool) will fill up faster, which can slightly impact performance on very large datasets.

2. Multilingual Support

If your application needs to store data in multiple languages (like Chinese, Arabic, or even Emojis), you must use nvarchar.

If you try to store a Chinese character (e.g., 语) in a standard varchar column without the correct collation, it will likely be converted into a question mark (?).

3. Literal Prefixing (N'...')

When inserting data into an nvarchar column, you should prefix your strings with N.

• Correct: INSERT INTO Users (Name) VALUES (N'José')

• Incorrect: INSERT INTO Users (Name) VALUES ('José')

  Without the N, SQL Server converts the string to the database's default code page before inserting it into the Unicode column, which can result in data loss for special characters.

4. Performance Gotcha: Implicit Conversion

One of the most common performance issues in SQL Server happens when you compare a varchar column to an nvarchar parameter (or vice versa).

Warning: If you have an index on a varchar column but your application sends an nvarchar value to filter it, SQL Server may be forced to convert the entire column to match the data type. This "Implicit Conversion" can prevent the database from using your index, causing queries to run much slower.

---

Which one should you use?

• Use nvarchar if there is any chance you will store international names, addresses, or special symbols. In modern development, this is often the "safe" default choice.

• Use varchar only if you are certain the data will only ever contain standard ASCII characters (like system codes, ZIP codes, or internal IDs) and you want to save space on massive tables.