nvarchar vs varchar

UTF-8 has been brought up here. I strongly encourage you to read below article before going that route. I.e., if you need to go outside your collation's normal character set and it is space saving you are after, then use Unicode and some compression option.

One nasty aspect, as Erland mentioned, is that you might think that varchar(20) means 20 characters. But it doesn't. And with UTF-8, you are far far more likely to encounter characters that requires more storage than what your length defined. Like räksmörgås using 13 bytes and won't fit in varchar(10). So, make sure you read below before venturing into the scary land of UTF-8:

https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/#guidance

As with most questions, it depends on your use case.

If you never are going to have non-english + some non-english chars, there is no reason to ever use NVARCHAR. It is just a waste of space. Keep in mind, this is disk and RAM space you are doubling.

If you don't know the answer to that question, then the default is NVARCHAR.

Hi SQLGuy10-3060,

Using nvarchar data type is easier to manage character data in international databases because nvarchar data type column can store any Unicode data. The disadvantage of nvarchar data type is that it may use up a lot of extra storage space.
Please refer to this article which might help.

Best Regards,
Amelia

It all depends. For columns that are to hold codes (Currency codes, transaction codes or whatever), varchar is probably the way to go, since such codes typically are in the ASCII range. No need for nvarchar.

When it comes to names, there is reason to sit back and think for a while. Maybe your business right now is only in, say the Americas and Western Europe, and you can get by with varchar to save some space. But all of a sudden, your business expands into, say, Poland. It is a bit of a bummer if you cannot store Polish names which needs a different code page. If you settled on varchar from the start, you have a lot work to clean this up. To the extent that it may not be possible. Thus, selecting nvarchar for name columns and similar may be more future-proof.

As Viorel points out, in SQL 2019 you can use UTF-8 which you store in varchar. The one thing to keep in mind here that when you say varchar(20) this is 20 bytes. A value like "räksmörgås" is 10 characters, but is 13 bytes in UTF-8.

According to documentation, you can use varchar instead of nvarchar for non-Latin text too if you choose UTF-8 collation: https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15#utf8.

Probably there are certain differences, in specific circumstances, between varchar with UTF-8 and nvarchar.