Differences between char, varchar, and varchar2

Charvarcharvarchar2 differences: 1. the length of CHAR is fixed, while the length of VARCHAR2 can be changed. For example, the storage string "abc", for CHAR (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum, when you store

Differences between char varchar varchar2: 1. the length of CHAR is fixed, while the length of VARCHAR2 can be changed. For example, the storage string "abc", for CHAR (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum, when you store

Differences between char varchar varchar2
Differences:
1. the length of CHAR is fixed, while the length of VARCHAR2 can be changed. For example, the storage string "abc", for CHAR (20 ), it indicates that the characters you store will occupy 20 bytes (including 17 null characters), while the same VARCHAR2 (20) takes up only 3 bytes, and 20 is the maximum value, when the characters you store are less than 20 characters, they are stored according to the actual length.
2. CHAR is more efficient than VARCHAR2.
3. Currently, VARCHAR is a synonym for varchar2. The industry standard VARCHAR type can store null strings, but oracle does not, although it reserves the right to do so in the future. Oracle has developed a data type VARCHAR2, which is not a standard VARCHAR. It changes the feature that can store NULL strings in the varchar column of the database to store NULL values. If you want backward compatibility, we recommend that you use VARCHAR2 instead of VARCHAR.

When should CHAR be used and varchar2 be used?
CHAR and VARCHAR2 are a contradictory unity, and they are complementary.
VARCHAR2 saves space than CHAR, and is slightly less efficient than CHAR. That is, to achieve efficiency, a certain amount of space must be sacrificed, this is what we often say in database design, "Change space for efficiency '.
Although VARCHAR2 saves space than CHAR, if a VARCHAR2 column is often modified and the length of the data to be modified is different each time, this will cause Row Migration, this causes redundant I/O, which should be avoided during database design and adjustment. In this case, it would be better to replace VARCHAR2 with CHAR.

Differences between char varchar nchar nvarchar
1. char [(n)]

Character data with a fixed length of n Bytes and is not Unicode. N must be a value between 1 and 8,000. The storage size is n Bytes. The synonym for char in the SQL-92 is character.

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


If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the CAST function, the default length is 30.


The default database sorting rules will be assigned to objects using char or varchar unless a specific sorting rule is assigned to the COLLATE clause. This sorting rule controls the code page used to store character data.


For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues. If char or varchar is used:


If you want the data values in the column to be close to the same size, use char.




If you want the data values in the column to be significantly different, use varchar.
If SET ANSI_PADDING is OFF when you execute create table or alter table, a char column defined as NULL will be processed as varchar.


When the collation code page uses double-byte characters, the storage size is still n Bytes. Depending on the string, the storage size of n Bytes may be less than n characters.

Nchar is the data type of Unicode data of a fixed length, nvarchar is the data type of Unicode data of a variable length, both use the UNICODE UCS-2 character set.
3. nchar (n)

Unicode data with a fixed length of n characters. The value of n must be between 1 and 4,000. The storage size is twice the size of n Bytes. The synonyms of nchar in the SQL-92 are national char and national character.

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

If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the CAST function, the default length is 30.


If you want the sizes of all data items in the column to be close to the same, use nchar.


Nvarchar is used if you want the data items in a column to vary greatly.


Objects that use nchar or nvarchar are assigned the default sorting rules of the database, unless the COLLATE clause is used to assign specific sorting rules.


SET ANSI_PADDING OFF is not applicable to nchar or nvarchar. SET ANSI_PADDING ON always applies to nchar and nvarchar.


========================================================== ==========================================

Nchar (n)


Unicode data with a fixed length of n characters. The value of n must be between 1 and 4,000. The storage size is twice the size of n Bytes. The synonyms of nchar in the SQL-92 are national char and national character.


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.


Note


If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the CAST function, the default length is 30.


If you want the sizes of all data items in the column to be close to the same, use nchar.


Nvarchar is used if you want the data items in a column to vary greatly.


Objects that use nchar or nvarchar are assigned the default sorting rules of the database, unless the COLLATE clause is used to assign specific sorting rules.


SET ANSI_PADDING OFF is not applicable to nchar or nvarchar. SET ANSI_PADDING ON always applies to nchar and nvarchar.
2. char and varchar
Data Types of fixed-length (char) or variable-length (varchar) characters.

Char [(n)]


Character data with a fixed length of n Bytes and is not Unicode. N must be a value between 1 and 8,000. The storage size is n Bytes. The synonym for char in the SQL-92 is character.

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.
Note
If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the CAST function, the default length is 30.

The default database sorting rules will be assigned to objects using char or varchar unless a specific sorting rule is assigned to the COLLATE clause. This sorting rule controls the code page used to store character data.

For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues. If char or varchar is used:

If you want the data values in the column to be close to the same size, use char.

If you want the data values in the column to be significantly different, use varchar.


If SET ANSI_PADDING is OFF when you execute create table or alter table, a char column defined as NULL will be processed as varchar.

When the collation code page uses double-byte characters, the storage size is still n Bytes. Depending on the string, the storage size of n Bytes may be less than n characters.


Summary:
1. varchar:
Variable-length non-Unicode data, which can contain a maximum of 8,000 characters.
2. nvarchar:
A variable-length Unicode data with a maximum length of 4,000 characters.
3. char:
A fixed length of non-Unicode characters. The maximum length is 8,000 characters.
4. nchar
Unicode data of a fixed length. The maximum length is 4,000 characters.

5. char and varchar are both string types.

A string encoded in Unicode. The result is an integer of the character.

========================================================== ========================================================== ========================================================== ==========

Char is fixed length, and varchar is variable length.

Varchar2 should be an upgrade to varchar. It seems that only ORACLE is available. We will not discuss it here.

Char fixed-length storage, fast speed, but there is a certain amount of space waste, suitable for scenarios where the field is not very large, high speed requirements. The speed is fast because it is physically stored based on a fixed length. In this way, you can retrieve fixed-length characters at a time based on the offset address.

Varchar is used for variable-length storage, so it is less efficient than char. During varchar storage, you must physically store the actual length of the field, and then the content. In this way, you need to read it twice, read its length once, and then read the content. Therefore, its access speed is slower than char. But it can save space.

Due to the characteristics of mysql, if a data table has a varchar field, the char field in the table is automatically converted to the varchar field. In this case, setting char is meaningless. Therefore, to use char for high efficiency, ensure that the varchar field does not exist in the table; otherwise, it should be set to varchar field.

Differences between char, varchar, text, nchar, nvarchar, and ntext in SQL
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.
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 pure English characters and numbers, use char/varchar.

------------------------

Which of the following types of char, nchar, varchar, nvarchar, text, and ntext fields are defined in the database?

When the database defines fields of the char type, I wonder if you will hesitate to select which of the following char, nchar, varchar, nvarchar, text, and ntext fields? There are two possible results. One is the choice of frugal people: it is best to use a fixed length to save some space for the change, and the process will be faster, if you cannot set the length, you have to set the length as small as possible. The other is that it doesn't matter. Try to use a variable type and enlarge the length as much as possible.
In view of the current favorable situation where hardware is as cheap as radish, it makes little sense to be entangled in such a small problem. However, if you do not find out about it, you will always feel sorry for the overworked CPU and hard disk.

(The following description is only valid for SqlServer ):
1. Use the following queries with caution when using non-unicode:
Select f from t where f = n'xx'
Cause: the index cannot be used, because the database will convert f to unicode first and then compare it with N 'xx '.
2. The processing speed of char and varchar with the same length is almost the same (as described later)
3. The length of varchar does not affect the processing speed !!! (See the following explanation)
4. The total length of a column in an index can be up to 900 bytes. Therefore, nchar cannot create an index for varchar, char, and nvarchar with a length greater than 900.
5. indexes cannot be created on text or ntext.
6. In O/R Mapping, the attribute types of the corresponding entities are generally strings, and char [] is rarely used. Therefore, if mapping is reasonable, the types of variable length are more consistent.
7. Generally, the name in the basic data table basically uses the like '% xx %' method in the actual query, but this method cannot use the index. Therefore, for this field, the index is also created in white.
8. Other fields like remark do not need to be queried at all, so no index is required.
9. The storage of varchar is the same as that of string, that is, length {block}. Therefore, the length of varchar is irrelevant to the occupied space.
10. For a fixed-length field, extra space is required to store the NULL identifier. Therefore, if a char field contains many NULL values, unfortunately, your occupied space is larger than that without NULL (but this is not too large, because the NULL mark is stored in bits, but if you have only one NULL in a row, you need to identify it, in this case, you will waste 1 byte of space and sin !), At this time, you can use special identifiers for storage, such as 'nv'
11. Same as above. Therefore, indexes cannot take effect for such NULL queries. If you use a NULL marker instead, congratulations, you can use the index.
12. The comparison costs of char and varchar are the same. Now the key is to look at the cost of their index search. Because the search policies are the same, we should compare who occupies less space. If the number of characters is small, the char length is smaller than that of varchar. However, if the number is small, varchar may be less than char, it also depends on the enrichment of the actual filling value, such as varchar (3) and char (3). In theory, it should be char faster, but if it is char (10) and varchar (10). If the enrichment is only 30%, it should be varchar in theory. Because varchar requires extra space to store the block length, as long as the length (1-fillfactor) is greater than the storage space (as if it is 2 bytes), it will be faster than char with the same length.
13. nvarchar is slower than varchar, and it will take up double space for non-unicode characters. Why is this type introduced? Yes, it is for internationalization. For unicode data, sorting rules do not work for them, rather than when unicode characters process data in different languages, the sorting rule must be specified to work normally, so n type is of this benefit.

Summary:
1. If the data size is very large, and the length can be determined by 100% and the storage is only ansi characters, then char
2. Determine whether the length is not necessarily an ansi character or nchar;
3. You are not sure about the length. If you want to query and use indexes, set them to 400 in nvarchar format;
4. There is nothing to say if you do not query it. Use nvarchar (4000)
5. If you have a strong character, you can only use 3 and 4, and occasionally use 1. After all, this is an additional explanation. It is equivalent to telling others that I must use X-bit data.