What is the difference between varchar and nvarchar?

13
  • What's the difference between using data types varchar and nvarchar ?
  • nvarchar exists in every SQL database?
  • Is there any significant difference in performance between the two?
  • Is there a criteria for using them?

And I saw that there is also the same with char and nchar . Does the same apply to them?

    
asked by anonymous 21.06.2017 / 15:13

2 answers

13

It has to do with character encoding. NVARCHAR is a multibyte type to store Unicode .

As far as I know only exists in SQL Server, it is not part of the standard. The question has tag of MySQL, but it does not accept this type.

There is an intrinsic difference in performance since the coding used by NVARCHAR has several disadvantages.

I adopt VARCHAR until I need NVARCHAR . In most of my problems they are enough. The use of Latin1 serves me well and gains space and performance. There are some who use NVARCHAR by default, or "just in case," I do not like this kind of attitude.

The same applies to the type of characters, the difference is that they will have fixed character sizes, while the others are variable-sized types. Not to be confused with the size in bytes that NCHAR may vary.

The NVARCHAR can have a number of bytes different from the number of characters, and depends on the encoding and collate used, it can be twice the size, it may depend on the content, coding. In% w / w% the number of bytes is the same as the number of characters, plus the overhead > control (currently 24 bytes), of course. So you can store fewer characters.

21.06.2017 / 15:22
6

The varchar datatype considers non-UNICODE characters, nvarchar, in contrast, works with UNICODE characters.

What you have to take into account is the amount stored by each data type.

VARCHAR will store the reported amount, plus 2bytes. For example, a VARCHAR (10) field will store a maximum of 10bytes + 2bytes. These two extra bytes are due to being a variable-sized data type.

NVARCHAR will occupy twice the space plus the 2bytes of control. So in the same example, an NVARCHAR (10) field will occupy 20bytes + 2bytes.

This will make a lot of difference to your storage and should be taken into account.

Source

Roughly, in the CHAR and VARCHAR world, each character occupies 1 byte. A byte is a set of 8 bits and considering all the positions of these bits (on and off) we can have 256 combinations (2 ^ 8). This means that one byte is capable of representing 256 different combinations. For the American alphabet this is more than enough, for the Latin alphabet this is also more than enough.

The problem begins when we consider Arabic, Asian, Greek, etc. alphabets. In this case, if we consider all possible letters and characters we will extrapolate all 256 combinations that 1 byte can represent. For this situation came the NVARCHAR and the NCHAR. For these types of data each character occupies 2bytes. If a byte can express 256 combinations (2 ^ 8), two bytes can store 65536 combinations (2 ^ 16). With this amount of combinations, it is possible to represent any existing character only the storage cost becomes larger.

If you use the CHAR and VARCHAR types and try to store certain characters, the universe of available characters will be restricted to the collation you have chosen. If you try to store another character that is not covered by this collation, that character will be converted to some approximate character. If you choose NCHAR and NVARCHAR, then this limitation does not occur.

Font

    
21.06.2017 / 16:53