Relationship between char, varchar, varchar2, and nvarchar in the database

The char type in the database is used to store fixed-length strings. The efficiency is slightly higher than that of varchar. However, it consumes more space than varchar. For example, if you store the string "ABC", for char (20), it means that the characters you store will occupy 20 bytes (including 17 null characters), and the same varchar2 (20) it takes up to 3 bytes of length, and 20 is the maximum value. When the character you store is less than 20 characters, the storage is based on the actual length.

In Oracle, there will be a data type of the varchar2 type, the difference between the varchar2 type and the varchar type:

1. varchar2 processes all characters in two bytes (generally). varchar only occupies two bytes of Chinese characters and full-angle characters, and each digit or English character is one byte;
2. varchar2 treats empty strings as null, while varchar still processes empty strings;
3. varchar2 characters must be stored in several bytes, depending on the character set used by the database,
In most cases, the varchar2 type is recommended to ensure better compatibility.

How to explain N in varchar (N) and nvarchar (n:
Nvarchar (n) can store up to n characters, which are not distinguished between Chinese and English.
Varchar (n) can store up to n Bytes. One Chinese character is two bytes.
Space occupied:
A single nvarchar (n) character occupies two bytes.
Varchar (n) occupies two bytes of Chinese, and English occupies one.
Value range of N:
Nvarchar (n) n ranges from 1 to 4000.
Varchar (n) n ranges from 1 to 8000.
Whether the size of N affects performance:
The length N in varchar and nvarchar does not affect the space size and performance. Unless N is Max and the content is greater than 4000 or 8000
Setting N is more of a business requirement. For example, if the ID card is limited to 18 characters, an error is reported if there are more than 18 characters, or the hard disk is prevented from being cracked by malicious attacks. It has no effect on space and performance.
N setting is better:
Since there is no impact on space and performance, we only need to consider the business needs. I have analyzed Microsoft databases, most of which are set to 256. We will also see 64,128,512, Max, etc, it may be easy to remember.
Examples of varchar (N) and nvarchar (n) buckets:
Unicode data with a variable length of n characters. 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 English, you can select varchar. If the field value contains many double-byte (Chinese, Korean, etc.) characters, nvarchar is used.
How do I select varchar and nvarchar?
In SQL Server, varchar stores data in a single byte. nvarchar uses Unicode to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (unicode encoding is generally used) and an English character is saved to the database. If the field type is varchar, only one byte is occupied, if the field type is nvarchar, it occupies two bytes.
Normally, we can use varchar to store Chinese characters. However, if the operating system is an English operating system and the Chinese font is not fully supported, if the Chinese character is varchar in SQL Server, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if varchar is used to store data, it cannot be found in the development stage. in most cases, there will be no problems during deployment.
But! If the deployed host is an English operating system and does not support the Chinese environment, the problem arises. All varchar fields are garbled when they are stored in Chinese ??). In general, you do not know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.
Another 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 increase by a factor of storage space. However, given the low storage cost, 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.

If both varchar (300) and varchar (8000) store the same number of characters, there is no difference in performance and the storage behavior is not different. Because they all have the same storage structure, two-byte offset, and two-byte columns (if all columns in the table are of the varchar type ). The difference lies in the storage capacity.
Most performance comparisons are concentrated on varchar, Char, varchar, and varchar (max. Also, out-of-Row Storage (supported by SQL Server 2005 ).

Varchar (max) (lob type) is different from varchar storage.
When the lob data is small enough, you can consider storing the data directly in the Data row (the data page where the row is located), so as to avoid additional lob page reading, improve the efficiency of Lob Data Access (set the threshold for storing lob data directly on the data page by the text in row option ).
When the lob data exceeds this threshold, or the row size exceeds 8060 bytes (the maximum size of a single row), the lob data is stored on the lob page, on the data page, a 16-byte pointer pointing to the lob page is retained. Of course, its access efficiency will be low.
In addition, malicious users can use this to "blow up" your disk.

 

Http://blog.csdn.net/cyq1984/article/details/8264100