SQL Server field char, varchar, text and nchar, nvarchar, ntext

1. 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. If the space is insufficient, it is automatically filled with null. Therefore, trim () may be used multiple times during reading (). 2. VARCHAR. Store variable-length data, but the storage efficiency is not

1. 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. If the space is insufficient, it is automatically filled with null. Therefore, trim () may be used multiple times during reading (). 2. VARCHAR. Store variable-length data, but the storage efficiency is not

1. 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. If the space is insufficient, it is automatically filled with spaces. Therefore, trim () may be used multiple times during reading ().


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


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


4. NCHAR, NVARCHAR, and NTEXT. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, 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 length of nchar and nvarchar is between 1 and 4000. 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 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 English letters and numbers, use char/varchar.
I will summarize their differences:
CHAR, NCHAR fixed length, fast speed, large space, need to be processed
VARCHAR, NVARCHAR, and TEXT cannot be long, the space is small, and the speed is slow. No processing is required.
NCHAR, NVARCHAR, and NTEXT process Unicode codes

Varchar occupies 2 bytes for each English (ASCII) character, and only two bytes for one Chinese Character

Char occupies 1 byte for English (ASCII) characters and 2 bytes for one Chinese Character

The Varchar type is not filled with spaces, for example, varchar (100). However, if its value is "n", its value is "n"

Char is different. For example, char (100) has a value of "n", but in fact it is "n" in the database. (there are 99 spaces after n, is to fill it with 100 bytes ).

Char is of a fixed length, so 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!

Note that for the encoded char (N)/varchar (N), N indicates the maximum number of characters allowed in the current column, rather than the number of bytes.

The text type can be divided into tinytext (255 length), smalltext (65535), midumtext (int maximum), and longtext (long maximum ).
In mysql, the storage of text data is somewhat different from that of varchar/char. text data is stored outside the metadata table, however, varchar/char is stored in the table data file together with other columns.
In fact, the text data type will greatly increase the size of the database table file, which has some impact on physical storage. Meanwhile, text data retrieval and IO output will also increase competition for memory.