char varchar varchar2 difference (RPM)

Char varchar varchar2 the difference http://blog.csdn.net/honglei_zh/article/details/7172538
Difference:
1. The length of the char is fixed, and the length of the VARCHAR2 is changeable, for example, storing the string "abc", for Char (20), which means that the characters you store will account for 20 bytes (including 17 null characters), while the same VARCHAR2 (20) consumes only 3 bytes of length. 20 is the maximum value, which is stored as the actual length when the character you store is less than 20 o'clock.
2. The efficiency of char is slightly higher than that of VARCHAR2.
3. Currently varchar is synonymous with VARCHAR2. The industry standard varchar type can store an empty string, but Oracle does not, although it retains the right to do so later. Oracle has developed a data type of VARCHAR2, which is not a standard varchar, and it will store null values in the database in which the varchar column stores the empty string. If you want to have backward compatibility capabilities, Oracle recommends using VARCHAR2 instead of varchar.

When should I use char and when should I use VARCHAR2?
Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations.
VARCHAR2 saves space than Char and is slightly less efficient than char, that is, to be efficient, you have to sacrifice a certain amount of space, which is what we often call "space-for-efficiency" in database design.
Although VARCHAR2 is more space-saving than char, if a VARCHAR2 column is often modified and the length of each modified data is different, this causes a ' row-migration ' phenomenon, which creates redundant I/O, which is to be avoided in database design and tuning. , it would be better to use char instead of VARCHAR2 in this case.

char varchar nchar nvarchar the difference between four
1, char[(n)]

A fixed-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. The storage size is n bytes. The synonym for Char in SQL-92 is character.

2, varchar[(n)]


Variable-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. Storage size is the actual length of bytes of input data, not n bytes. The input data character length can be zero. The synonym for varchar in SQL-92 is char varying or character varying.


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.


The default collation of the database is assigned to an object that uses char or varchar, unless a specific collation is otherwise assigned with the COLLATE clause. This collation controls the code page used to store character data.


Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:


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




If you want the data value in the column to be significantly different in size, use varchar.
If SET ansi_padding is OFF when the CREATE table or ALTER table is executed, a char column that is defined as NULL is treated as varchar.


When a 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 are data types for fixed-length Unicode data, and nvarchar are data types for variable-length Unicode data, both using the Unicode UCS-2 character set.
3, nchar (n)

A fixed-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size is twice times that of n bytes. The synonyms for nchar in SQL-92 are national char and national character.

3, nvarchar (n)

A variable-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered. The input data character length can be zero. The synonyms for nvarchar in SQL-92 are national char varying and national character varying.

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.


Use nchar if you want all data items in the column to be close to the same size.


Use nvarchar if you want the size of the data items in the column to vary widely.


objects that use nchar or nvarchar are given the default collation of the database, unless a specific collation is assigned using the COLLATE clause.


SET ansi_padding OFF does not apply to nchar or nvarchar. SET ansi_padding on is always available for nchar and nvarchar.


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

NCHAR (n)


A fixed-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size is twice times that of n bytes. The synonyms for nchar in SQL-92 are national char and national character.


nvarchar (n)


A variable-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. The storage size of bytes is twice times the number of characters entered. The input data character length can be zero. The synonyms for nvarchar in SQL-92 are national char varying and national character varying.


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.


Use nchar if you want all data items in the column to be close to the same size.


Use nvarchar if you want the size of the data items in the column to vary widely.


objects that use nchar or nvarchar are given the default collation of the database, unless a specific collation is assigned using the COLLATE clause.


SET ansi_padding OFF does not apply to nchar or nvarchar. SET ansi_padding on is always available for nchar and nvarchar.
Two, char and varchar
Fixed-length (char) or variable-length (varchar) character data types.

char[(N)]


A fixed-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. The storage size is n bytes. The synonym for Char in SQL-92 is character.

varchar[(N)]
Variable-length, non-Unicode character data with a length of n bytes. n must be a numeric value between 1 and 8,000. Storage size is the actual length of bytes of input data, not n bytes. The input data character length can be zero. The synonym for varchar in SQL-92 is char varying or character varying.
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.

The default collation of the database is assigned to an object that uses char or varchar, unless a specific collation is otherwise assigned with the COLLATE clause. This collation controls the code page used to store character data.

Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:

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

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


If SET ansi_padding is OFF when the CREATE table or ALTER table is executed, a NULL-defined The char column will be treated as varchar.

When a 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.


Summarize:
1. VarChar:
Variable-length non-Unicode data with a maximum of 8,000 characters.
2, nvarchar:
Variable-length Unicode data with a maximum length of 4,000 characters.
3, Char:
Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
4, nchar
Fixed-length Unicode data with a maximum length of 4,000 characters.

5, char and varchar are string types.

A Unicode-encoded string that results in an integer value of the character

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

Char is fixed-length, and varchar is variable-length.

VARCHAR2 should be a varchar upgrade, it seems that only Oracle, not discussed here.

Char fixed-length storage, fast, but there is a certain amount of space waste, applicable to the field is not very large, high speed requirements of the occasion. The speed is fast because it is physically stored in a fixed length, so that it is possible to remove the character at one time based on the offset address.

varchar is not as efficient as char because it becomes longer storage. When varchar is stored, it physically stores the actual length of the field before it is content. It reads two times, reads its length, and then the content. So it will be slower to access than char. But it can save space.

Due to the characteristics of MySQL itself, if a data table has a varchar field, the Char field in the table will automatically be converted to a varchar field. In this case, the char set is meaningless. Therefore, to take advantage of the high efficiency of char, ensure that there is no varchar field in the table, otherwise, it should be set to a varchar field.

Differences between char, varchar, text, and nchar, nvarchar, ntext in SQL
1, CHAR. Char is convenient for storing fixed-length data, and the index on a char field is highly efficient, such as defining char (10), which takes up 10 bytes of space regardless of whether the data you store is 10 bytes.
2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used.
From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.
3, TEXT. Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.
4, NCHAR, NVARCHAR, NTEXT. These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. It can be seen that the use of nchar, nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.
So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar.

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

What kind of char, nchar, varchar, nvarchar, text, ntext are defined in a database to a field of type char?

When a database is defined to a field of type char, I wonder if you would hesitate to choose char, nchar, varchar, nvarchar, text, ntext. The result is likely to be two, one is the choice of frugal people: it is better to use fixed length, the feeling is longer than the length can save some space, and the processing will be faster, can not be fixed long have to choose the length of the set as small as possible;
In view of the current hardware as cheap as the good situation of radish, entanglement such a small problem is not much significance, but if not understand it, always feel sorry overworked CPU and hard disk.

Here's the beginning (the following instructions are only valid for SQL Server):
1. When using non-Unicode, use the following query with caution:
Select F from t where F = N ' xx '
Reason: The index cannot be exploited because the database converts F to Unicode and then to n ' xx ' comparison
2, char and the same length of varchar processing speed is similar (after the explanation)
3, the length of varchar will not affect the processing speed!!! (See explanation below)
4, the total column length in the index supports a total of 900 bytes, so varchar, char, and Nvarchar,nchar greater than 900 will not be able to create the index.
5, text, ntext is unable to create the index
6, O/R mapping the corresponding entity's attribute type is generally string majority, with char[] very few, so if the rationality of mapping, variable length of the type more consistent
7, the name in the General base table in the actual query is basically all used like '%xx% ' this way, and this way is not able to use the index, so if for this kind of field, the index is built also white
8. Some other fields like remark do not need to be queried at all, so the index is not required
9, varchar storage and string is the same principle, that is, length {block} This way, so the length of varchar and it actually occupies space is irrelevant
10. For fixed-length fields, additional space is required to hold the null identifier, so if there is a large number of NULL in a char field, then unfortunately, you occupy more space than the non-null (but this big is not too much, because the null ID is stored with bit, But if you are the only one in a row you need to identify the null, then you wasted 1byte space, sin sin! At this time, you can use a special logo to store, such as: ' NV '
11, ibid, so for this null query, the index is not effective, if you use a null token substitution, then congratulations, you can use the index
12, char and varchar compare cost is the same, now the key is to look at the cost of their index lookup, because the search strategy is the same, so should compare who occupy small space. In the case of holding the same number of characters, if the number is small, then char occupies a length less than varchar, but if the number is slightly larger, then varchar is probably less than char, and depends on the actual fill value of the fullness, such as varchar (3) and char (3), So theoretically it should be char fast, but if it is char (10) and varchar (10), the fullness of only 30% of the case, theoretically it should be varchar fast. Because varchar requires extra space to hold the block length, as long as length (1-fillfactor) is greater than the storage space (as if it were 2 bytes), it will be faster than char of the same length.
13. Nvarchar is slower than varchar, and it takes up double space for non-Unicode characters, so what is this type of launch for? Yes, it's for internationalization. For Unicode types of data, collations do not work for them, and non-Unicode characters must specify collations to work correctly when working with data in different languages, so the n type is a bit of a benefit.

Summarize:
1, if the amount of data is very large, and can be 100% to determine the length and save only ANSI characters, then Char
2, can determine the length is not necessarily ANSI character or, then use nchar;
3, uncertain length, to query and want to use the index, with the nvarchar type bar, set them to 400;
4, do not query the words of nothing to say, with nvarchar (4000)
5, the character forthright can only use 3 and 4, occasionally use 1, after all, this is an additional explanation, equals to tell others that I must need the length of x-bit data

char varchar varchar2 difference (RPM)