[ACCEPTED]-varchar or nvarchar-variables

Accepted answer
Score: 19

Basically, nvarchar means you can handle 13 lots of alphabets, not just regular English. Technically, it 12 means unicode support, not just ANSI. This 11 means double-width characters or approximately 10 twice the space. These days disk space is 9 so cheap you might as well use nvarchar 8 from the beginning rather than go through 7 the pain of having to change during the 6 life of a product.

If you're certain you'll 5 only ever need to support one language you 4 could stick with varchar, otherwise I'd 3 go with nvarchar.

This has been discussed 2 on SO before here.

EDITED: changed ascii to ANSI 1 as noted in comment.

Score: 7

First of all, to clarify, nvarchar stores unicode 19 data while varchar stores ANSI (8-bit) data. They 18 function identically but nvarchar takes 17 up twice as much space.

Generally, I prefer 16 storing user names using varchar datatypes unless 15 those names have characters which fall out 14 of the boundary of characters which varchar can 13 store.

It also depends on database collation 12 also. For e.g. you'll not be able to store 11 Russian characters in a varchar field, if your 10 database collation is LATIN_CS_AS. But, if you are 9 working on a local application, which will 8 be used only in Russia, you'd set the database 7 collation to Russian. What this will do 6 is that it will allow you to enter Russian 5 characters in a varchar field, saving some space.

But, now-a-days, most 4 of the applications being developed are 3 international, so you'd yourself have to 2 decide which all users will be signing up, and 1 based on that decide the datatype.

Score: 2

I have red that nvarchar takes twice as 3 varchar.


nvarchar is used for internationalization.


what 2 u suggest should i use nvarchar or varchar?

It's 1 depends upon the application.

Score: 1

By default go with nvarchar. There is very 3 little reason to go with varchar these days, and 2 every reason to go with nvarchar (allows 1 international characters; as discussed).

Score: 1

varchar is 1 byte per character, nvarchar 9 is 2 bytes per character.

You will use more 8 space with nvarchar but there are many more 7 allowable characters. The extra space is 6 negligible, but you may miss those extra 5 characters in the future. Even if you don't 4 expect to require internationalization, people 3 will often have non-English characters (e.g. é, ñ 2 or ö) in their names.

I would suggest you 1 use nvarchar.

Score: 1

I have red that nvarchar takes twice as 26 varchar

Yes. According to Microsoft: "Storage 25 size, in bytes, is two times the number 24 of characters entered + 2 bytes" (http://msdn.microsoft.com/en-us/library/ms186939(SQL.90).aspx).

But 23 storage is cheap; I never worry about a 22 few extra bytes.

Also, save yourself trouble 21 in the future and set the maximum widths 20 to something more generous, like 100 characters. There 19 is absolutely no storage overhead to this 18 when you're using varchar or nvarchar (as 17 opposed to char/nchar). You never know when 16 you're going to encounter a triple-barrelled 15 surname or some long foreign name which 14 exceeds 30 characters.

nvarchar is used for 13 internationalization.

nvarchar can store 12 any unicode character, such as characters 11 from non-Latin scripts (Arabic, Chinese, etc). I'm 10 not sure how your application will be taking 9 data (via the web, via a GUI toolkit, etc) but 8 it's likely that whatever technology you're 7 using supports unicode out of the box. That 6 means that for any user-entered data (such 5 as name) there is always the possibility of receiving 4 non-Latin characters, if not now then in 3 the future.

If I was building a new application, I 2 would use nvarchar. Call it "future-proofing" if 1 you like.

Score: 0

The nvarchar type is Unicode, so it can 16 handle just about any character that exist 15 in every language on the planet. The characters 14 are stored as UTF-16 or UCS-2 (not sure 13 which, and the differences are subtle), so 12 each character uses two bytes.

The varchar 11 type uses an 8 bit character set, so it's 10 limited to the 255 characters of the character 9 set that you choose for the field. There 8 are different character set that handles 7 different character groups, so it's usually 6 sufficient for text local to a country or 5 a region.

If varchar works for what you want 4 to do, you should use that. It's a bit less 3 data, so it's overall slightly faster. If 2 you need to handle a wide variety of characters, use 1 nvarchar.

Score: 0

on performance:
a reason to use varchar over nvarchar is 5 that you can have twice as many characters 4 in your indexes! index keys are limited 3 to 900 bytes
on usability:
if the application is only 2 ever intended for a english audience & contain 1 english names, use varchar

Score: 0

Data to store: "Sunil"

varchar(5) takes 7B nvarchar(5) takes 1 12B

More Related questions