The difference between varchar, VARCHAR2, char, and nvarchar in Oracle

1.char

The length of char is fixed, for example, you define char (20), even if you insert ABC, less than 20 bytes, the database will automatically add 17 spaces after ABC to complement 20 bytes;
Char is a distinction between English and Chinese, which occupies two bytes in char, and English occupies one, so char (20) You can only save 20 letters or 10 characters.
Char is suitable for fixed length, generally does not contain Chinese situation

2.varchar/varchar2

VarChar is not fixed in length, for example, you define varchar (20), and when you insert ABC, it only occupies 3 bytes in the database.
varchar also distinguishes between English and Chinese, and this is the same as Char.

VARCHAR2 is basically the same as varchar, which is a non-industry standard varchar defined by Oracle itself, except that VARCHAR2 uses NULL instead of the empty string of varchar
VARCHAR/VARCHAR2 is suitable for non-fixed length, generally does not contain Chinese situation

3.nvarchar/nvarchar2

Nvarchar and nvarchar2 are not fixed in length.
Nvarchar does not distinguish between Chinese and English, such as: you define nvarchar (20), you can deposit 20 English letters/kanji or Chinese and English combinations, this 20 defines the number of characters rather than the number of bytes
NVARCHAR2 is basically the same as nvarchar, except that the English alphabet, which is stored in nvarchar2, also accounts for two bytes.
Nvarchar/nvarchar2 for storing Chinese

4.char [(N)]
Fixed-length, non-Unicode character data with n bytes in length. The values for n range from 1 to 8,000, and the storage size is n bytes.

varchar [(n | max)]
Variable-length, non-Unicode character data. The values for n range from 1 to 8,000. Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the input data plus 2 bytes, which reflects the length of the stored data. The length of the input data can be 0 characters.
* Use char if the column data items are of the same size.
* varchar is used if the size of the column data items is significantly different.
* Use varchar (max) If the column data item size varies greatly, and the size may exceed 8,000 bytes.

If n is not specified in a data definition or variable declaration statement for char or varchar data type, the default length is 1. If the char or varchar data type does not specify n when using the CAST and CONVERT functions, the default length is 30.
When the CREATE table or ALTER table is executed, if SET ansi_padding is OFF, the char column defined as NULL is treated as varchar.

5. Data retrieval
The data retrieval of the varchar type is slightly better than the scan of char, regardless of whether it is indexed.

To select char or to choose a varchar suggestion
1. Conditions suitable for char:
A. The length of each row in the column is basically the same, and the length varies by no more than 50 bytes;
B. Data changes are frequent and there is less demand for data retrieval.
C. The length of the column does not change, and the cost of modifying the width of the char type column is relatively high.
D. A large number of null values do not appear in the column.
E. There is no need to create too many indexes on the column, and too many indexes have a large impact on data changes in the Char column.

2. Conditions suitable for varchar;
A. The length of the rows in the column varies significantly.
B. There are very few updates to the data in the column, but the queries are very frequent.
C. Columns often have no data, null values or null values
nchar [(N)]
A fixed-length Unicode character data of n characters. The n value must be between 1 and 4,000 (inclusive). The storage size is twice times n bytes.
nvarchar [(n | max)]
Variable-length Unicode character data. The n value is between 1 and 4,000 (inclusive). Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is twice times the number of characters entered and 2 bytes. The length of the input data can be 0 characters.

Comments:
If n is not specified in a data definition or variable declaration statement, the default length is 1. If n is not specified using the CAST function, the default length is 30.
If the size of the column data item may be the same, use nchar.
If the size of the column data items may vary widely, use nvarchar.
sysname is a system-supplied user-defined data type that is functionally identical to nvarchar (128) Except for non-nullable values. The sysname is used to reference the database object name.
The default database collation is assigned to objects that use nchar or nvarchar, but you can use the COLLATE clause to assign a specific collation.
SET ansi_padding on is always available for nchar and nvarchar. SET ansi_padding OFF does not apply to nchar or nvarchar data types.

The difference between varchar, VARCHAR2, char, and nvarchar in Oracle