Basic introduction and difference of varchar and nvarchar in SQL

Time:2021-10-12

varchar(n)

Variable length non Unicode character data with a length of N bytes. N must be a number between 1 and 8000. The storage size is the actual length of bytes of input data, not n bytes.

nvarchar(n)

Variable length Unicode character data containing n characters. The value of N must be between 1 and 4000. The storage size of bytes is twice the number of characters entered.

The two fields have field values: me and coffee

Then the varchar field accounts for 2 × 2 + 6 = 10 bytes of storage space, while nvarchar field accounts for 8 × 2 = 16 bytes of storage space.

If the field value is only in English, varchar can be selected, and nvarchar is used when there are many double byte (Chinese, Korean, etc.) characters in the field value

The above is a summary introduction. You can know from the above introduction.

Varchar (4) can input four letters or two Chinese characters

Nvarchar (4) can input four Chinese characters and four letters at most

Differences among char, varchar, nchar and nvarchar

For string fields in the program, there are four types of char, varchar, nchar and nvarchar in SQL Server (text and ntext are not considered for the time being). These four types are often vague in the database. Here is a comparison.

Fixed length or variable length

The so-called fixed length means that the length is fixed. When the length of the input data does not reach the specified length, it will be automatically filled with English spaces to make the length reach the corresponding length; If there is a VAR prefix, it means that the actual storage space is variable. For example, varchar and nvarchar variable length character data will not be filled with spaces. The exception is that text storage is also variable length.

Unicode or non Unicode

In the database, English characters only need one byte to store, but Chinese characters and many other non English characters need two bytes to store. If English and Chinese characters exist at the same time, it is easy to cause confusion due to the different number of occupied space, resulting in the read string is garbled. Unicode character set is produced to solve the incompatibility of character set. All its characters are represented by two bytes, that is, English characters are also represented by two bytes. The prefix n represents Unicode characters, such as nchar and nvarchar. These two types use the Unicode character set.

Let’s look at the field capacity based on the above two points

Char, varchar up to 8000 English and 4000 Chinese characters
Nchar and nvarchar can store 4000 characters, both in English and Chinese

Use (personal preference)

If the amount of data is very large, the length can be 100% determined, and the save is only ANSI characters, then char
If you can determine the length but not necessarily ANSI characters or, use nchar;
For very large data, such as article content, use ntext

Other general nvarchar

Comparison of char, varchar, nchar and nvarchar

CHAR

Char is very convenient to store fixed length data. The index efficiency level on char field is high. For example, if char (10) is defined, whether the data you store reaches 10 bytes or not, it will take up 10 bytes of space.

VARCHAR

Variable length data is stored, but the storage efficiency is not as high as char. If the possible value of a field is not of fixed length, we only know that it cannot exceed 10 characters. It is most cost-effective to define it as varchar (10). The actual length of a varchar type is the actual length of its value + 1. Why “+ 1”? This byte is used to save the actual length used.
In terms of space, varchar is suitable; In terms of efficiency, char is appropriate. The key is to find the trade-off point according to the actual situation.

TEXT

Textstores variable length non Unicode data with a maximum length of 2 ^ 31-1 (2147483647) characters.

NCHAR、NVARCHAR、NTEXT

These three are more “n” than the first three in terms of names. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese; Char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you don’t have to worry about whether the input characters are English or Chinese. It is more convenient, but there is some loss in the quantity of storing English.
So generally speaking, if it contains Chinese characters, use nchar / nvarchar. If it is pure English and numbers, use char / varchar.

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]