What is the difference between varchar and nvarchar?

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

varchar: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using.

nvarchar: Variable-length Unicode character data. Dependent on the database collation for comparisons.

Armed with this knowledge, use whichever one matches your input data (ASCII v. Unicode).

I always use nvarchar as it allows whatever I'm building to withstand pretty much any data I throw at it. My CMS system does Chinese by accident, because I used nvarchar. These days, any new applications shouldn't really be concerned with the amount of space required.

nvarchar stores data as Unicode, so, if you're going to store multilingual data (more than one language) in a data column you need the N variant.

varchar is used for non-Unicode characters only on the other hand nvarchar is used for both unicode and non-unicode characters. Some other difference between them is given bellow.

VARCHAR vs. NVARCHAR

You're right. nvarchar stores Unicode data while varchar stores single-byte character data. Other than storage differences (nvarchar requires twice the storage space as varchar), which you already mentioned, the main reason for preferring nvarchar over varchar would be internationalization (i.e. storing strings in other languages).

Since SQL Server 2019 varchar columns support UTF-8 encoding.

Thus, from now on, the difference is size.

In a database system that translates to difference in speed.

Less data = Less IO + Less Memory = More speed in general. Read the article above for the numbers.

Go for varchar in UTF8 from now on!

Only if you have big percentage of data with characters in ranges 2048 - 16383 and 16384 – 65535 - you will have to measure

nVarchar will help you to store Unicode characters. It is the way to go if you want to store localized data.

I would say, it depends.

If you develop a desktop application, where the OS works in Unicode (like all current Windows systems) and language does natively support Unicode (default strings are Unicode, like in Java or C#), then go nvarchar.

If you develop a web application, where strings come in as UTF-8, and language is PHP, which still does not support Unicode natively (in versions 5.x), then varchar will probably be a better choice.

I have to say here (I realise that I'm probably going to open myself up to a slating!), but surely the only time when NVARCHAR is actually more useful (notice the more there!) than VARCHAR is when all of the collations on all of the dependant systems and within the database itself are the same...? If not then collation conversion has to happen anyway and so makes VARCHAR just as viable as NVARCHAR.

To add to this, some database systems, such as SQL Server (before 2012) have a page size of approx. 8K. So, if you're looking at storing searchable data not held in something like a TEXT or NTEXT field then VARCHAR provides the full 8k's worth of space whereas NVARCHAR only provides 4k (double the bytes, double the space).

I suppose, to summarise, the use of either is dependent on:

• Project or context
• Infrastructure
• Database system

Jeffrey L Whitledge with ~47000 reputation score recommends usage of nvarchar

Solomon Rutzky with with ~33200 reputation score recommends: Do NOT always use NVARCHAR. That is a very dangerous, and often costly, attitude / approach.

What are the main performance differences between varchar and nvarchar SQL Server data types?

https://www.sqlservercentral.com/articles/disk-is-cheap-orly-4

Both persons of such a high reputation, what does a learning sql server database developer choose?

There are many warnings in answers and comments about performance issues if you are not consistent in choices.

There are comments pro/con nvarchar for performance.

There are comments pro/con varchar for performance.

I have a particular requirement for a table with many hundreds of columns, which in itself is probably unusual ?

I'm choosing varchar to avoid going close to the 8060 byte table record size limit of SQL*server 2012.

Use of nvarchar, for me, goes over this 8060 byte limit.

I'm also thinking that I should match the data types of the related code tables to the data types of the primary central table.

I have seen use of varchar column at this place of work, South Australian Government, by previous experienced database developers, where the table row count is going to be several millions or more (and very few nvarchar columns, if any, in these very large tables), so perhaps the expected data row volumes becomes part of this decision.

varchar is suitable to store non-unicode which means limited characters. Whereas nvarchar is superset of varchar so along with what characters we can store by using varchar, we can store even more without losing sight of functions.

Someone commented that storage/space is not an issue nowadays. Even if space is not an issue for one, identifying an optimal data type should be a requirement.

It's not only about storage! "Data moves" and you see where I am leading to!