The basic introduction and differences between varchar and nvarchar in SQL, varcharnvarchar

The basic introduction and differences between varchar and nvarchar in SQL, varcharnvarchar

Varchar (n)

Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes.

Nvarchar (n)

Unicode data with a variable length of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters.

The two fields have Field Values: Me and coffee.

The varchar field occupies 2 × 2 + 6 = 10 bytes of storage space, while the nvarchar field occupies 8 × 2 = 16 bytes of storage space.

If the field value is only in English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, use nvarchar.

The above is a summary and can be seen through the above introduction.

Varchar (4) can contain 4 letters or two Chinese characters

Nvarchar (4) can contain four Chinese characters or four letters, but a maximum of four

Differences between char, varchar, nchar, and nvarchar

For string fields in the program, SQLServer contains char, varchar, nchar, and nvarchar types (text and ntext are not considered for the time being, these four types are often vague. Here is a comparison.

Fixed Length or variable length

The so-called fixed length is a fixed length. when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length; if there is a var prefix, it indicates that the actual storage space is longer. For example, varchar and nvarchar variable-length data are not filled with spaces. The exception is that text storage is also variable-length.

Unicode or non-Unicode

In the database, only one byte is required for English characters, but two bytes are required for Chinese characters and many other non-English characters. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The prefix n indicates Unicode characters, such as nchar and nvarchar. These two types Use the Unicode Character Set.

Based on the above two points to see the field capacity

Char and varchar can contain up to 8000 English letters and 4000 Chinese Characters
Nchar and nvarchar can store 4000 characters, regardless of English or Chinese Characters

Usage (personal preferences)

If the data size is very large and can be 100% characters long and only ansi characters are saved, char
The length cannot be ansi characters or nchar;
For ultra-large data, such as article content, use nText

Other general nvarchar

Comparison of char, varchar, nchar, and nvarchar features

CHAR

It is very convenient for CHAR to store fixed-length data, and the indexing efficiency of CHAR fields is high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space.

VARCHAR

It stores variable-length data, but the storage efficiency is not as high as CHAR. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters, defining it as VARCHAR (10) is the most cost-effective. The actual length of the VARCHAR type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length.
From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation.

TEXT

Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

NCHAR, NVARCHAR, NTEXT

The three names are named N more than the first three ". 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 letters and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.
Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains pure English characters and numbers, use char/varchar.

What is the difference between SQL server varchar and nvarchar?

In the statements generated in SQL SERVER, add N before the string. The N prefix must be an uppercase letter. It is the meaning of Unicode encoding. Generally, English characters are composed of one byte, but there are too many words in the world. Therefore, two bytes are used to represent characters to meet international needs.
Adding N to the front of the string indicates that the string is stored in Unicode format when it is stored in the database. N 'string' indicates that string is a Unicode string.
The format of a Unicode string is similar to a regular string, but it is preceded by an N identifier (N stands for the international Language in the SQL-92 standard )). The N prefix must be an uppercase letter. For example, 'michél' is a String constant, while n'michél' is a Unicode constant. Unicode constants are interpreted as Unicode data and are not calculated using the code page. Unicode constants do have sorting rules, mainly used to control comparison and Case sensitivity. Assign the default sorting rules of the current database to Unicode constants unless the sorting rules are specified for them using the COLLATE clause. Each character in Unicode data is stored in two bytes, while each character in character data is stored in one byte. For more information, see use Unicode data.
Unicode string constants support enhanced sorting rules.
Select ''a' -- output 'A' escape using ''in SQL statements. Other characters can be directly entered.
The best nvarchar for storing Chinese Characters
Varchar is the best option for storing only English and numbers.

1. If there is a var prefix, it indicates that the actual storage space is longer. varchar and nvarchar
The so-called fixed length is a fixed length. when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length; the variable-length character data is not filled with spaces. The exception is that text is also variable-length.

2. if there are n prefixes, n indicates Unicode characters, that is, all characters occupy two bytes. In nchar and nvarchar characters, only one byte is required for English characters, but there are many Chinese characters, it requires two bytes of storage, which may cause confusion when both English and Chinese characters exist. The Unicode Character Set is generated to solve the incompatibility problem of the character set. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes.
3. Check the field capacity based on the above two points
Char and varchar can contain up to 8000 English letters and 4000 Chinese Characters
Nchar and nvarchar can store 4000 characters, regardless of English or Chinese Characters
Varchar and nvarchar selection ("n" prefix)
When varchar storage contains non-English characters (such as Chinese and Japanese), "?" is displayed. Garbled

A good advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings. of course, using nvarchar to store English characters will multiply the storage space. however, when the storage cost is low, the compatibility will bring you more benefits.
Therefore, you should try to use nvarchar to store data during Design. varchar is used only when you ensure that this field does not save Chinese characters.

Differences between nvarchar and varchar in SqlServer 1. Definitions:
1. nvarchar (n): Contains variable-length Unicode characters of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The length of the entered data is... the remaining full text>
 
What are the differences between varchar and nvarchar in SQL field attributes?

Varchar [(n)]

Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes. The length of the input data can be zero. The synonym for varchar in the SQL-92 is char varying or character varying.

Nvarchar (n)

Unicode data with a variable length of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The length of the input data can be zero. The synonym for nvarchar in the SQL-92 is national char varying and national character varying.
Varchar [(n)]

Variable-length and non-Unicode character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes. The length of the input data can be zero. The synonym for varchar in the SQL-92 is char varying or character varying.

Nvarchar (n)

Unicode data with a variable length of n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The length of the input data can be zero. The synonym for nvarchar in the SQL-92 is national char varying and national character varying.