I am storing first name and last name with up to 30 characters each. Which is better varchar
or nvarchar
.
I have read that nvarchar
takes up twice as much space compared to varchar
and that nvarchar
is used for internationalization.
So what do you suggest should I use: nvarchar
or varchar
?
Also please let me know about the performance of both. Is performance for both is same or they differ in performance. Because space is not too big issue. Issue is the performance.
char and varchar cannot store Unicode characters. char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space. varchar and nvarchar are variable-length which will only use up spaces for the characters you store.
Each character of an nvarchar column requires 2 bytes of storage whereas a varchar column requires 1 byte per character. Potentially, varchar will be quicker but that may well mean that you cannot store the data that you need.
Use nvarchar when the sizes of the column data entries vary considerably. Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.
The NVARCHAR data type stores character data in a variable-length field. Data can be a string of single-byte or multibyte letters, digits, and other characters that are supported by the code set of your database locale.
Basically, nvarchar means you can handle lots of alphabets, not just regular English. Technically, it means unicode support, not just ANSI. This means double-width characters or approximately twice the space. These days disk space is so cheap you might as well use nvarchar from the beginning rather than go through the pain of having to change during the life of a product.
If you're certain you'll only ever need to support one language you could stick with varchar, otherwise I'd go with nvarchar.
This has been discussed on SO before here.
EDITED: changed ascii to ANSI as noted in comment.
First of all, to clarify, nvarchar
stores unicode data while varchar
stores ANSI (8-bit) data. They function identically but nvarchar takes up twice as much space.
Generally, I prefer storing user names using varchar
datatypes unless those names have characters which fall out of the boundary of characters which varchar
can store.
It also depends on database collation also. For e.g. you'll not be able to store Russian characters in a varchar
field, if your database collation is LATIN_CS_AS
. But, if you are working on a local application, which will be used only in Russia, you'd set the database collation to Russian. What this will do is that it will allow you to enter Russian characters in a varchar
field, saving some space.
But, now-a-days, most of the applications being developed are international, so you'd yourself have to decide which all users will be signing up, and based on that decide the datatype.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With