What is the difference between varchar and nvarchar?

Question

Is it just that nvarchar supports multibyte characters? If that is the case, is there really any point, other than storage concerns, to using varchars?

I like incomudro's point, it's what led me to digging around about the difference between varchar & nvarchar in the first place. Our Java app against a SQL Server db uses myBatis, which seems to send strings as nvarchar by default (still not sure how (or if) that's overrideable). A simple query was showing up as a huge performance problem because I'd defined the column it was selecting against as varchar, not nvarchar, and it was ignoring the index on the column. — Sean Read, May 02 '13 at 16:59

score 2095 · Accepted Answer · edited Jun 15 '13 at 08:32

2095

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.

edited Jun 15 '13 at 08:32

lazyCrab

187
17

answered Sep 29 '08 at 02:16

Jeffrey L Whitledge

58,241
9
71
99

11

This is great info to have. So am I understanding this correctly if I deduce that the choice ultimately becomes one of--which resource is cheaper: processor + development overhead or storage? – Matt Cashatt Jan 15 '12 at 01:34
175

@MatthewPatrickCashatt - You could see it that way. But if you imagine a glorious world in which *all* text data is in Unicode, and developers simply don't ever have to think about what encoding something is in, and a whole class of errors simply never occur, then you can see that there is really no choice at all. – Jeffrey L Whitledge Jan 18 '12 at 19:21
22

[varchar is not restricted to an 8 bit code page in all collations](http://stackoverflow.com/a/8250586/73226) – Martin Smith Feb 05 '12 at 23:20
10

@Martin Smith - In those cases, the tiny advantage that varchar confers (compact storage) vanishes. I guess varchar is even worse than I thought! – Jeffrey L Whitledge Feb 06 '12 at 00:53
4

@JeffreyLWhitledge : Comment #1 : Do you think your answer still holds for data warehouse purposes? The document on [this page](http://msdn.microsoft.com/en-us/library/hh146876.aspx) suggests `"Only use nchar and nvarchar when the universe of values spans or will span multiple languages."`. For data warehousing purposes, shouldn't disk i/o and network bandwidth also come into consideration? – Adrian Torrie May 10 '13 at 01:51
4

@JeffreyLWhitledge : Comment #2 :If you think in terms of ([link](http://sqlanywhere-forum.sybase.com/questions/9232/sql-anywhere-millionbillion-row-scenario)) `"With 1 billion rows, every wasted byte per row costs you 1GB, which you also have to backup, recover, and index."` would you still consider codepage incompatabilities to be the determining factor in data-type choice/selection? – Adrian Torrie May 10 '13 at 01:52
8

@iValueValue - So they are advocating code pages as a means of data compression? If I were to compress data, I would want to do so in a way that doesn't compromise the integrity of that data. There are plenty of ways to do that without mangling your character data, as it would be mangled in a code page conversion. UTF-8 would be a good start. – Jeffrey L Whitledge May 10 '13 at 03:44
6

@iValueValue - Also, data warehouse design in general doesn't sweat over the occasional 1GB "wasted" here and there. A typical star-schema is designed to waste *tons* of space by de-normalizing data. I am not a fan of such schemes, as they are a misuse of a perfectly good RDBMS system. But, assuming it is a good idea to do so, once you've decided to abandon good relational database design for a data warehouse, having wildly uncompressed data is hardly an issue to start being worried about. – Jeffrey L Whitledge May 10 '13 at 03:50
1

Be aware that once you've decided to store your data "as Unicode", there is a still a question what the best Unicode representation is. nvarchar strings usually are in effect UTF-16, so the principle advantage it has over UTF-8 is that it makes it easier to interact with other components that use UTF-16 (e.g. Windows system calls or 16 bit collation). It is not "really unicode" any more or less than UTF-8 is "really unicode", although it does still beat ASCII. And aside from compressing English text, the advantage of UTF-8 is the same as UTF-16: interaction with other UTF-8 components... – Steve Jessop Jun 07 '13 at 10:45
3

"An nvarchar column can store any Unicode data." This is not true, though it would be easy to get that impression from most MSSQL documentation. The encoding used internally is UCS-2, which can store only data in Unicode's "Basic Multilingual Plane". Characters outside this plane cannot be stored directly in a nchar or nvarchar field w/o additional processing. – PeterAllenWebb Jun 27 '13 at 04:30
9

@PeterAllenWebb - You can “store” any Unicode data, because the surrogate pairs in UTF-16 can be stored in UCS-2 as though they were characters. That will work transparently for data storage and retrieval. Now, what you can’t do is get reliable case transformations and comparisons outside the BMP, but I didn’t make any claims about that. So if you have a lot of Desseret text that you want to do processing on, it would be best to do that outside of the database. But it’s just fine for storing it there. (Of course, varchar isn't going to help you there either!) – Jeffrey L Whitledge Jun 27 '13 at 13:27
What is the difference between Ascii(character) and Ascii(string)?? .Sql processed all values as varbinary or binary??? – sarathkumar Jul 04 '14 at 13:10
4

I'm seeing the exact opposite than this with performance. Reading 2k nvarchars is about a third slower than reading 2k varchars. I'm using a SSD to minimize i/o. So conversion is a lot faster than i/o, which makes sense in my mind because conversion isn't dependent on i/o, and i/o will always be the slowest part in all of this. – Paw Baltzersen Jul 07 '14 at 09:50
5

@PawBaltzersen - That sounds about right to me. Clearly, reading 400,000 bytes is going to take longer than reading 200,000 bytes. The fact that the nvarchar version didn't take twice as long may be partially explained by the extra time necessary to do the code page conversion. My argument, however, isn't that Unicode is faster. My argument is that it is correct. And to me, correct trumps fast every time. – Jeffrey L Whitledge Jul 07 '14 at 16:03
4

Where I work, we have 12000 databases that each have millions of rows. These databases only operate in the US and UK. It would be ludicrous to spend the kind of money you are talking about on storage for some nonexistent "correctness". We have never had anything be "incorrect". – PRMan Oct 09 '14 at 21:32
7

@PRMan - You don't say what kind of data your databases store, but if that includes people or place names, then it is a simple fact that no single (non-Unicode) code page covers all of the characters necessary for the data to be correct. This is true even for just the US or the UK. When you say that you have never had anything be incorrect, I wonder how you know that. Are you saying that the system has never gone down because of an encoding error? I will accept that. Are you saying that nobody has ever complained about the system mangling their name? I will accept that too. – Jeffrey L Whitledge Oct 09 '14 at 21:56
4

(cont.) Are you saying that nobody has ever given up trying to enter their name correctly and just accepted the mangling that took place? That may not have happened. But if it has, then you have no way to know. I submit that you probably have no idea what errors have crept into your database. The machines keep humming, so the data is fine. I also bet you read your printed newspaper by the light of an incandescent bulb, and that all works great too. – Jeffrey L Whitledge Oct 09 '14 at 21:57
2

Any examples you can offer us Jeffrey? In my part of the world (Scandinavia) the biggest concern is usually to select the correct collation (to get æ/ä,ø/ö,å in the right order). Varchar stores our special chars just fine (albeit as two bytes). Since ~90-95% of the chars are usually plain ascii, that translates to nearly 50% of character fields storing zeros if we use nvarchar. Where I work we actually did switch to nvarchar, and the first question from our hw-guys were "why are the dbs now twice their usual size?". I suspect our move was a mistake and I would love to learn otherwise. – 9Rune5 Aug 08 '15 at 22:16
Just to answer my own comment: The collation I chose do not support UTF-8. I am used to working with a DBMS (SQL Anywhere) which does use UTF-8 and that will naturally trigger different strategies when it comes to NVARCHAR vs VARCHAR. Thus: NVARCHAR for me. – 9Rune5 Oct 16 '15 at 09:23
Am I correct in thinking that as a general rule, nvarchar should be used for data, where as varchar and char would be used when indexing as creating relationships as they might provide extra performance that makes the big differences? – jwrightmail Feb 23 '18 at 19:46
1

@jwrightmail It is slightly possible that the performance could be affected for those kinds of applications by the choice of one character set or the other, and it is also possible (but not certain) that the improvement would favor a varchar character set. But I still wouldn't do it. Mostly because (as shown on this answer) I seem to have a religious devotion to Unicode and an automatic opinion that all non-Unicode character sets are evil. If you don't share my religion, then other options are available, but I may secretly judge you for it. Sorry. – Jeffrey L Whitledge Feb 26 '18 at 19:38
1

I agree. The greater speed one might believe they can achieve by using VARCHAR is quickly destroyed completely when facing a modern platform where just a single part doesn't embrace the VARCHAR. An example is the mssql-jdbc driver which will convert any bound VARCHAR parameter to NVARCHAR causing indeces to be useless on any VARCHAR-column. Only way to fix this? Set a property that will make the driver use VARCHAR for all params, but this will not be compatible with your NVARCHAR columns in db. Easier fix? Just always use NVARCHAR. – Kent Munthe Caspersen Jan 05 '22 at 10:10

user7116 · Answer 2 · 2008-09-27T19:42:24.153

307

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).

edited Sep 27 '08 at 19:42

answered Sep 27 '08 at 19:37

user7116

63,008
17
141
172

5

Is there a restriction like varchar can't store Unicode data ? Its all 1's and 0's . I am able to save Chinese content as varchar just fine to my DB . I just specify its UTF-8 though . How does that work then ? – Nishant Sep 24 '14 at 14:24
5

@Nishant late _answer_: of course you can store UTF-8 in varchar but it'll break SQL Server string functions. If you perform all searches/transformations within your application then yes, you may do it (but what's the benefit?). Only Unicode encoding supported by SS is UCS-2 (yes, not UTF-16 before SS2k16) and its string functions work only with that encoding. BTW what about indices? If you want to store arbitrary data you'd better use binary instead. – Adriano Repetti Sep 09 '15 at 07:16
1

Yes it just breaks the String Search Functions. – Nishant Sep 11 '15 at 15:13
17

So, you know...it doesn't "work". That's like storing a `float` into an `int` and going, "well sure the decimals go missing." Just don't. – user7116 Sep 11 '15 at 18:56
Probably in an e-commerce platform where you know beforehand the categories content one can use *varchar* for only-English or "standard" western names, and somewhere else where you have names, locations, product descriptions content *nvarchar* would be some better choice – Eve Dec 14 '21 at 00:52

score 81 · Answer 3 · edited Dec 10 '16 at 13:53

81

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.

edited Dec 10 '16 at 13:53

Peter Mortensen

30,738
21
105
131

answered Sep 27 '08 at 19:37

tags2k

82,117
31
79
106

41

The idea that new apps shouldn't be concerned with space restrictions is somewhat short-sighted, and anyone who has dealt with databases at the medium-to-large enterprise level will be happy to tell you, completely incorrect. – Frater Jul 21 '10 at 06:19
85

To take the liberty of putting words in tags2k's mouth, I think a more accurate statement might be 'it's increasingly unlikely that any new apps should be more concerned about the space required than they should be about internationalisation and other character set issues'. – Cowan Oct 15 '10 at 21:38
2

"These days, any new apps shouldn't really be concerned with the amount of space required." - Unless you are using free cloud storage, where the paid plan is a CONSIDERABLE jump in $ (see AppHarbor SQL Server shared plans). – ganders Jun 06 '14 at 13:50
4

@ganders Howl! You're right there. Generalised statements are only ever temporarily correct at best. Computing is definitely a swings and roundabout game. I'm definitely concerned with how much space I'm using on Windows Azure CCP. That said I would "never" use varchar over nvarchar. Ooo did I just contradict myself? – rism Jun 08 '14 at 03:43
1

@rism, I believe you removed any risk of contradiction with your use of quotes on `"never"`, at least technically. – Smandoli Nov 05 '14 at 16:02
Space is usually the lesser of the concerns when looking at space vs time vs accuracy, space has gotten relatively cheap, being responsive is usually a bigger concern, and accuracy is at the top (aside from in things like streaming and big data) That doesn't mean doubling space needs isn't going to cause concerns, and bigger data can cause slowdowns. – Andrew Nov 09 '21 at 14:48
However why nobody thought instead at decreasing the space used by 'nvarchar' in a form of some alternative, at least the unused space taken when storing each 'nvarchar' record ? Isn't that a global problem, even connected to the global warming as well if big data computers mainly work for just querying bigger databases? It's understandable that many 'correct' coders might not care too much about the cost pains of our end customers, however the solution probably might come again from the coders fellowship too. – Eve Dec 14 '21 at 01:01

Jeremy Frank · Answer 4 · 2010-10-07T19:06:49.327

It depends on how Oracle was installed. During the installation process, the NLS_CHARACTERSET option is set. You may be able to find it with the query SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'.

If your NLS_CHARACTERSET is a Unicode encoding like UTF8, great. Using VARCHAR and NVARCHAR are pretty much identical. Stop reading now, just go for it. Otherwise, or if you have no control over the Oracle character set, read on.

VARCHAR — Data is stored in the NLS_CHARACTERSET encoding. If there are other database instances on the same server, you may be restricted by them; and vice versa, since you have to share the setting. Such a field can store any data that can be encoded using that character set, and nothing else. So for example if the character set is MS-1252, you can only store characters like English letters, a handful of accented letters, and a few others (like € and —). Your application would be useful only to a few locales, unable to operate anywhere else in the world. For this reason, it is considered A Bad Idea.

NVARCHAR — Data is stored in a Unicode encoding. Every language is supported. A Good Idea.

What about storage space? VARCHAR is generally efficient, since the character set / encoding was custom-designed for a specific locale. NVARCHAR fields store either in UTF-8 or UTF-16 encoding, base on the NLS setting ironically enough. UTF-8 is very efficient for "Western" languages, while still supporting Asian languages. UTF-16 is very efficient for Asian languages, while still supporting "Western" languages. If concerned about storage space, pick an NLS setting to cause Oracle to use UTF-8 or UTF-16 as appropriate.

What about processing speed? Most new coding platforms use Unicode natively (Java, .NET, even C++ std::wstring from years ago!) so if the database field is VARCHAR it forces Oracle to convert between character sets on every read or write, not so good. Using NVARCHAR avoids the conversion.

Bottom line: Use NVARCHAR! It avoids limitations and dependencies, is fine for storage space, and usually best for performance too.

This is a really good answer, except that the question is about sql-server. — stimms, Oct 07 '10 at 21:42
Best answer. I'm using varchar2, because my db character set is al32utf8 — lalilulelo_1986, Aug 11 '21 at 20:39

score 31 · Answer 5 · edited Dec 10 '16 at 13:54

31

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.

edited Dec 10 '16 at 13:54

Peter Mortensen

30,738
21
105
131

answered Sep 27 '08 at 19:36

albertein

26,396
5
54
57

score 27 · Answer 6 · answered Mar 01 '21 at 14:17

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

	VARCHAR	NVARCHAR
Character Data Type	Variable-length, non-Unicode characters	Variable-length, both Unicode and non-Unicode characters such as Japanese, Korean, and Chinese.
Maximum Length	Up to `8,000 characters`	Up to `4,000 characters`
Character Size	Takes up `1 byte` per character	Takes up `2 bytes` per Unicode/Non-Unicode character
Storage Size	Actual Length (in bytes)	2 times Actual Length (in bytes)
Usage	Used when data length is variable or variable length columns and if actual data is always way less than capacity	Due to storage only, used only if you need Unicode support such as the Japanese Kanji or Korean Hangul characters.

score 25 · Answer 7 · edited Aug 07 '21 at 19:53

The main difference between Varchar(n) and nvarchar(n) is:

Varchar ( Variable-length, non-Unicode character data) size is upto 8000.

It is a variable length data type
Used to store non-Unicode characters
Occupies 1 byte of space for each character

Nvarchar: Variable-length Unicode character data.

It is a variable-length data type
Used to store Unicode characters.
Data is stored in a Unicode encoding. Every language is supported. (for example the languages Arabic, German,Hindi,etc and so on)

score 20 · Answer 8 · edited Dec 10 '16 at 13:47

My two cents

Indexes can fail when not using the correct datatypes:
In SQL Server: When you have an index over a VARCHAR column and present it a Unicode String, SQL Server does not make use of the index. The same thing happens when you present a BigInt to a indexed-column containing SmallInt. Even if the BigInt is small enough to be a SmallInt, SQL Server is not able to use the index. The other way around you do not have this problem (when providing SmallInt or Ansi-Code to an indexed BigInt ot NVARCHAR column).
Datatypes can vary between different DBMS's (DataBase Management System):
Know that every database has slightly different datatypes and VARCHAR does not means the same everywhere. While SQL Server has VARCHAR and NVARCHAR, an Apache/Derby database has only VARCHAR and there VARCHAR is in Unicode.

But surely if you're writing your code properly (i.e. using parameterised queries etc) then point 1 is less of a risk. — Paul, Nov 20 '13 at 11:21

score 18 · Answer 9 · edited Dec 10 '16 at 13:49

Mainly nvarchar stores Unicode characters and varchar stores non-Unicode characters.

"Unicodes" means 16-bit character encoding scheme allowing characters from lots of other languages like Arabic, Hebrew, Chinese, Japanese, to be encoded in a single character set.

That means unicodes is using 2 bytes per character to store and nonunicodes uses only one byte per character to store. Which means unicodes need double capacity to store compared to non-unicodes.

Alexander Bartosh · Answer 10 · 2022-05-18T07:20:18.247

17

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

edited May 18 '22 at 07:20

answered Oct 05 '20 at 15:53

Alexander Bartosh

8,287
1
21
22

3

A significantly underrated answer. I wonder how many new databases are going to use nvarchar because an engineer only read the top answer from 2008. – kamilk Mar 29 '22 at 11:21
1

UTF8 have some limitation you can't use that collation with OLTP (SQLServer 2019) so if MEMORY_OPTIMIZED tables and NATIVE_COMPILATION of your procedure is mandatory better don't rely on UTF8. – ColdCat Oct 18 '22 at 14:59
@kamilk, not underrated, just not that usefull. There are a lot of us that deal with older SQL platforms that don't support that behaviour. its 2019 and above. Also the world is bigger that you think. UT8 is to small for a lot of cases where a product has to function internationally. IO is cheap, so NVARCHAR is the superior option. – zu1b Apr 14 '23 at 08:21

Mike Spross · Answer 11 · 2008-10-02T01:38:03.017

13

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).

edited Oct 02 '08 at 01:38

answered Sep 27 '08 at 19:42

Mike Spross

7,999
6
49
75

score 10 · Answer 12 · answered Sep 27 '08 at 19:36

10

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

answered Sep 27 '08 at 19:36

Vijesh VP

4,508
6
30
32

score 10 · Answer 13 · edited Dec 10 '16 at 13:52

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.

score 9 · Answer 14 · edited Jun 20 '20 at 09:12

If a single byte is used to store a character, there are 256 possible combinations, and thereby you can save 256 different characters. Collation is the pattern which defines the characters and the rules by which they are compared and sorted.

1252, which is the Latin1 (ANSI), is the most common. Single-byte character sets are also inadequate to store all the characters used by many languages. For example, some Asian languages have thousands of characters, so they must use two bytes per character.

Unicode standard

When systems using multiple code pages are used in a network, it becomes difficult to manage communication. To standardize things, the ISO and Unicode consortium introduced the Unicode. Unicode uses two bytes to store each character. That is 65,536 different characters can be defined, so almost all the characters can be covered with Unicode. If two computers use Unicode, every symbol will be represented in the same way and no conversion is needed - this is the idea behind Unicode.

SQL Server has two categories of character datatypes:

non-Unicode (char, varchar, and text)
Unicode (nchar, nvarchar, and ntext)

If we need to save character data from multiple countries, always use Unicode.

score 9 · Answer 15 · edited Oct 23 '17 at 16:37

Although NVARCHAR stores Unicode, you should consider by the help of collation also you can use VARCHAR and save your data of your local languages.

Just imagine the following scenario.

The collation of your DB is Persian and you save a value like 'علی' (Persian writing of Ali) in the VARCHAR(10) datatype. There is no problem and the DBMS only uses three bytes to store it.

However, if you want to transfer your data to another database and see the correct result your destination database must have the same collation as the target which is Persian in this example.

If your target collation is different, you see some question marks(?) in the target database.

Finally, remember if you are using a huge database which is for usage of your local language, I would recommend to use location instead of using too many spaces.

I believe the design can be different. It depends on the environment you work on.

score 8 · Answer 16 · answered Jan 05 '17 at 11:49

I had a look at the answers and many seem to recommend to use nvarchar over varchar, because space is not a problem anymore, so there is no harm in enabling Unicode for little extra storage. Well, this is not always true when you want to apply an index over your column. SQL Server has a limit of 900 bytes on the size of the field you can index. So if you have a varchar(900) you can still index it, but not varchar(901). With nvarchar, the number of characters is halved, so you can index up to nvarchar(450). So if you are confident you don't need nvarchar, I don't recommend using it.

In general, in databases, I recommend sticking to the size you need, because you can always expand. For example, a colleague at work once thought that there is no harm in using nvarchar(max) for a column, as we have no problem with storage at all. Later on, when we tried to apply an index over this column, SQL Server rejected this. If, however, he started with even varchar(5), we could have simply expanded it later to what we need without such a problem that will require us to do a field migration plan to fix this problem.

Allan F · Answer 17 · 2019-04-09T05:20:00.130

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.

score 6 · Answer 18 · answered Nov 20 '13 at 11:06

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

score 6 · Answer 19 · edited Dec 10 '16 at 13:43

6

Follow Difference Between Sql Server VARCHAR and NVARCHAR Data Type. Here you could see in a very descriptive way.

In generalnvarchar 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.

edited Dec 10 '16 at 13:43

Peter Mortensen

30,738
21
105
131

answered Jan 29 '14 at 17:22

Pradeep Kesharwani

1,480
1
12
21

This is a very useful link, but your answer doesn't amount to much more than that: a link. – RubberDuck Oct 07 '14 at 16:53
ckuhn203 ,I am not going to tell you to see this one – Pradeep Kesharwani Oct 08 '14 at 06:30

Rinoy Ashokan · Answer 20 · 2018-03-22T12:51:07.787

2

nvarchar is safe to use compared to varchar in order to make our code error free (type mismatching) because nvarchar allows unicode characters also. When we use where condition in SQL Server query and if we are using = operator, it will throw error some times. Probable reason for this is our mapping column will be difined in varchar. If we defined it in nvarchar this problem my not happen. Still we stick to varchar and avoid this issue we better use LIKE key word rather than =.

edited Mar 22 '18 at 12:51

answered Aug 10 '17 at 07:26

Rinoy Ashokan

1,501
17
14

difference between like and = is support for varchar and nvarchar – yolob 21 Nov 18 '20 at 14:35

score 1 · Answer 21 · answered Jul 21 '22 at 15:51

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!

What is the difference between varchar and nvarchar?

21 Answers21

VARCHAR vs. NVARCHAR

Unicode standard

Linked

Related