Difference between char, nchar, varchar and Nvarchar in Sqlserver

1. char type:

1 byte for English (ASCII) characters and 2 bytes for one Chinese character. It is convenient to store fixed-length data for CHAR, And the indexing efficiency on CHAR fields is extremely high, for example, if char (10) is defined, whether or not the data you store reaches 10 bytes will occupy 10 bytes of space. Because it is a fixed length, the speed 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. Because it is a fixed length, the speed is high.

2. VARCHAR stores variable-length data

If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10. 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.
Varchar type: the type of Varchar is not filled with spaces, but Char is filled up until it is full, such as varchar (100), but its value is only "qian ", therefore, the value stored in the database is "qian", and char is different, for example, char (100). Its value is "qian ", in fact, it is "qian" in the database (there are 96 spaces after qian, that is, it is filled with 100 bytes ).
Note: Since char is of a fixed length, it is much faster than varchar! However, it is a little troublesome for the program to process it. We need to use functions such as trim to remove spaces on both sides!
3. What is the difference between the Nchar and Nvarchar types?

For conversion from other types of characters, such as Chinese characters and phonetic symbols, each English (ASCII) character occupies two bytes and one Chinese Character occupies two bytes, all characters occupy 2 bytes.

Varchar (n): variable-length character data type. The maximum storage length is 8,000 characters.
Nvarchar (n): a variable-length Unicode data with a maximum length of 4,000 characters. The size of bytes is twice the number of input characters, that is, it stores data in two bytes. If data is stored in a single word segment, it also occupies storage space in double bytes.

Varchar is generally applicable to English and numbers. Nvarchar is applicable to Chinese and other characters, where N represents Unicode constants and can solve the conversion problem between multilingual character sets.

The following is a supplement:

For general string fields in the program, SQL Server has char, varchar, nchar, and nvarchar types. What are the differences between these four types? Here we will make a comparison.

1. Fixed Length or variable length

The so-called fixed length is fixed length. when the length of the data to be saved is insufficient, an English space will be automatically filled behind it to make the length reach the corresponding length. If there is a var prefix, it indicates that the actual storage space is dynamically changed. For example, varchar and nvarchar variable-length data are not filled with spaces.

2. 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.

3. maximum storage capacity of several data types

Char and varchar can contain up to 8000 English letters and 4000 Chinese Characters

Nchar and nvarchar can store a maximum of 4000 characters, regardless of English or Chinese Characters